Using ARRAY_AGG to Create Arrays in BigQuery

ARRAY_AGG is a powerful BigQuery function that allows you to store multiple values in a single field. This is useful for data analysis, as it allows you to easily manipulate multiple values at once. It also makes it easier to store related data in a single field, which can be useful for certain types of data analysis.

The ARRAY_AGG function takes a set of values and creates an array from them. It takes two arguments - the values to be added to the array and an optional delimiter. The delimiter is used to separate the values in the array. The default delimiter is a comma, but it can be any character or string.

Once the array has been created, it can be manipulated in the same way as any other array. For example, you can use the ARRAY_LENGTH function to get the length of the array, or the ARRAY_CONTAINS function to check if a value is in the array.

Examples of using ARRAY_AGG to create arrays in BigQuery

To illustrate how ARRAY_AGG works, let's look at a few examples. In the first example, we'll create an array from a set of values.


SELECT ARRAY_AGG(1,2,3)

This query will return the following array: [1,2,3].

In the second example, we'll create an array from a set of values, using a delimiter.


SELECT ARRAY_AGG(1,2,3, DELIMITER=';')

This query will return the following array: [1;2;3].

Additional info about using ARRAY_AGG to create arrays in BigQuery

It's important to note that ARRAY_AGG is specific to BigQuery. Other databases, such as MySQL and PostgreSQL, have their own functions for creating arrays. It's important to check the documentation for the database you're using to make sure you're using the correct syntax.

If you want to learn more about ARRAY_AGG, you can check out the official documentation here.

Want to build your own LLM Apps with AirOps👇👇