Advanced UNNEST Across Multiple Array Columns in BigQuery

3
votes

question about UNNEST. I have this table:enter image description here

I want to unnest based on the status and locale arrays but the result table still needs to have 7 rows, NOT 14. I want to unnest those "array pairs", not the 2 arrays one by one (that would return 14 rows).

Also, I don't the know the count of items in the arrays. It can be 1 but it also can be 20 but every array in the "group" does have the same number of items.

Any ideas of how to achieve that?

(keep in mind that I have around 10 columns as arrays but my sample image is showing only 2 of them)

1
google-bigquery
I've tried to understand the question, but I still don't really get what's the input and the desired outcome. – Felipe Hoffa
Mikhail got it right. Basically, I wanted to unpivot multiple columns at once. – Daniel Zrůst

1 Answers

8
votes

Below is for BigQuery Standard SQL 

#standardSQL
SELECT * EXCEPT(status, locale, pos1, pos2), status, locale  
FROM `project.dataset.table`,
UNNEST(status) status WITH OFFSET pos1,
UNNEST(locale) locale WITH OFFSET pos2
WHERE pos1 = pos2