Flatten multiple same-sized array columns in BigQuery table

Question

I have a table with several columns where some of them are arrays of the same length. I would like to unnest them to get a result with values from arrays in separate rows.

So having table like this one:

I would like to get to:

This is how it works for one of those array columns:

WITH data AS
(
  SELECT 1001 as id, ['a', 'b', 'c'] as array_1, [1, 2, 3] as array_2
  UNION ALL
  SELECT 1002 as id, ['d', 'e', 'f', 'g'] as array_1, [4, 5, 6, 7] as array_2
  UNION ALL
  SELECT 1003 as id, ['h', 'i'] as array_1, [8, 9] as array_2
)
SELECT id, a1
FROM data,
UNNEST(array_1) as a1

Is there some elegant way how to unnest both arrays at once? I would like to avoid unnesting each column separately and then joining everything together.

score 6 · Answer 1 · answered Nov 05 '19 at 13:48

6

Below is for BigQuery Standard SQL

#standardSQL
SELECT id, a1, a2
FROM data, UNNEST(array_1) AS a1 WITH OFFSET 
JOIN UNNEST(array_2) AS a2 WITH OFFSET
USING(OFFSET)

answered Nov 05 '19 at 13:48

Mikhail Berlyant

165,386
8
154
230

score 2 · Answer 2 · answered Nov 05 '19 at 11:02

You can use with offset and a join:

WITH data AS
(
  SELECT 1001 as id, ['a', 'b', 'c'] as array_1, [1, 2, 3] as array_2
  UNION ALL
  SELECT 1002 as id, ['d', 'e', 'f', 'g'] as array_1, [4, 5, 6, 7] as array_2
  UNION ALL
  SELECT 1003 as id, ['h', 'i'] as array_1, [8, 9] as array_2
)
SELECT id, a1, a2
FROM data cross join
     UNNEST(array_1) as a1 with offset n1 JOIN
     UNNEST(array_2) as a2 with offset n2 
     on n1 = n2

score 1 · Accepted Answer · answered Nov 06 '19 at 09:42

So I did a little research about unnesting in SQL on my own and came up with this solution:

WITH data AS
(
  SELECT 1001 as id, ['a', 'b', 'c'] as array_1, [1, 2, 3] as array_2
  UNION ALL
  SELECT 1002 as id, ['d', 'e', 'f', 'g'] as array_1, [4, 5, 6, 7] as array_2
  UNION ALL
  SELECT 1003 as id, ['h', 'i'] as array_1, [8, 9] as array_2
)
SELECT id, a1, array_2[OFFSET(off)] AS a2
FROM data
CROSS JOIN UNNEST(array_1) AS a1 WITH OFFSET off

The advantage is that it does not require to unnest all arrays, just the one.

Thanks, this solved a similar problem I have. Could you please point to docs to read more about the offset keyword? I don't seem to understand how it works in this context. — christinabo, Nov 17 '20 at 11:34

Flatten multiple same-sized array columns in BigQuery table

3 Answers3