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-28 18:50:10Z

5

#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, 2020 at 18:50

answered Jun 28, 2020 at 18:30

Mikhail Berlyant

174k10 gold badges173 silver badges251 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

user_a27 Over a year ago

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.

Mikhail Berlyant Over a year ago

see addition in my answer

user_a27 Over a year ago

Thank you so much. exactly I was looking for.

Yun Zhang · Accepted Answer · 2020-06-28 18:31:33Z

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, 2020 at 18:31

Yun Zhang

5,6032 gold badges14 silver badges30 bronze badges

1 Comment

user_a27 Over a year ago

Thanks.This is helpful.

Collectives™ on Stack Overflow

How to flatten nested array data into row in bigquery

2 Answers 2

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related