How to flatten nested array data into row in bigquery

user_9558906 0
Viewed 1386

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

unnest google-bigquery
2 Answers
#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
user_52219440

You can do following

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