Advanced UNNEST Across Multiple Array Columns in BigQuery

Question

question about UNNEST. I have this table:

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)

Answer 1

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