How to convert BigQuery column to array

How can I convert column to array in BqgQuery ?

BigQuery(BQ) is very useful for data analysis or processing.

It is good at handling huge data. It returns summary result in short time.

BQ has one useful data format array.

It can handle multiple data like [1,2,3] in 1 row.

But how can we create array data from column data.

So today I introduce about "How to convert BigQuery column to array".

Author

Mid-carieer engineer (AI, system). Good at Python and SQL.

Data

First, we have to prepare data.

Import this CSV data below as table array_sample.

key,val
a,1
a,2
a,3
b,2
b,4
b,5
c,3
c,2
c,-1

So 2-column-table would be created.

How to convert BigQuery column to array

In order to convert BigQuery column to array, we can use ARRAY_AGG().

ARRAY_AGG() is aggregate function that returns array.

You can use this function like below.

SELECT
key,
ARRAY_AGG(val) as val_array
FROM test.array_sample
GROUP BY key

This SQL returns result below.

Per key, values are aggregated to array.

Conclusion

Today I explained about "How to convert BigQuery column to array".

In order to convert column to array in BigQuery, we can take this solution.

It is easy to understand because we can use GROUP BY clause.

There are some other articles about BigQuey.

If you interested in them, please read them.