
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

Advantage to read
You can understand "How to convert BigQuery column to array". Then you don't have to concern about array data.
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.
SQL
SELECT key, ARRAY_AGG(val) as val_array FROM test.array_sample GROUP BY key
This SQL returns result below.
Result
Row | key | val_array |
1 | a | 1 |
2 | ||
3 | ||
2 | b | 2 |
4 | ||
5 | ||
3 | c | 3 |
2 | ||
-1 |
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.
Point
Use aggregate function ARRAY_AGG()
.

GROUP BY
clause.BigQuery Book
There are some other articles about BigQuey.
If you interested in them, please read them.
Read more