
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".
Prepare 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.
Row | key | val_array |
---|---|---|
1 | a | 1 |
2 | ||
3 | ||
2 | b | 2 |
4 | ||
5 | ||
3 | c | 3 |
2 | ||
-1 |
Values are aggregated to array by key.
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.
- Use aggregate function
ARRAY_AGG()
.



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