array-to-columns.md

Array to Column

Explore this snippet with some demo data here.

Description

Taking a BigQuery ARRAY and creating a column for each value is commonly used when working with 'wide' data. The following snippet will let you quickly take an array column and create a row for each value.

SELECT 
   select <columns to keep>, array_value
FROM
   `project.schema.table`
CROSS JOIN UNNEST(<array_column>) as array_value

where:

• <columns to keep> is a list of the columns from the original table you want to keep in your query results
• <array_column> is the name of the ARRAY column

Usage

The following example shows how to 'explode' 2 rows with 3 array elements each into a separate row for each array entry:

WITH demo_data as (
  select 1 row_index,[1,3,4] array_col union all 
  select 2 row_index, [3,6,9] array_col 
)
select demo_data.row_index, array_value
  FROM demo_data
  cross join UNNEST(array_col) as array_value

row_index	array_col
1	1
1	3
1	4
2	3
2	6
2	9

References & Helpful Links:

• UNNEST in BigQuery
• Arrays in Standard SQL