My BigQuery table has some regular columns plus one array of structs.
I'd like to run a query to flatten this structure, having one row for each element of the array, and duplicated values for the other columns.
I tried UNNEST but the result is not what I need:
SELECTcol_a, col_b, arrayFROM`table`,UNNEST(array)This query shows the value for col_a and col_b only once, and are null for the remaining rows of the array.
How can I change the query so that col_a and col_b are repeated for each row?
Best Answer
Use below
SELECTcol_a, col_b, elFROM`table`,UNNEST(array) as el