How to flatten nested array data into row in bigquery

Question

enter image description here

I am trying to flatten inside_array or sub array of nested array data into table rows. I am able to flatten array_data which is outside array. Anybody have any suggestion.Thanks in advance

Mikhail Berlyant · Accepted Answer · 2020-06-28T18:50:10.887

4

#standardSQL
SELECT ... 
FROM `project.dataset.table`,
UNNEST(array_data) AS array_data_rec,
UNNEST(array_data_rec.inside_array) AS inside_array_rec

To handle "no data inside the inside_array" - use LEFT JOIN instead as in below example

#standardSQL
SELECT ... 
FROM `project.dataset.table`,
UNNEST(array_data) AS array_data_rec
LEFT JOIN UNNEST(array_data_rec.inside_array) AS inside_array_rec

edited Jun 28 '20 at 18:50

answered Jun 28 '20 at 18:30

Mikhail Berlyant

165,386
8
154
230

Thanks this is helpful.Does it return no data if the there is no data in inside_array_rec and I try to fetch all column from table. – user_a27 Jun 28 '20 at 18:46
see addition in my answer – Mikhail Berlyant Jun 28 '20 at 18:50
Thank you so much. exactly I was looking for. – user_a27 Jun 28 '20 at 19:02

score 0 · Answer 2 · answered Jun 28 '20 at 18:31

0

You can do following

...
FROM
   AA.nested_array,
   UNNEST(array_data) as array_data,
   UNNEST(array_data.inside_array) as array_data_inside_array

answered Jun 28 '20 at 18:31

Yun Zhang

5,185
2
10
29

Thanks.This is helpful. – user_a27 Jun 28 '20 at 18:47

How to flatten nested array data into row in bigquery

2 Answers2