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:
SELECT
col_a, col_b, array
FROM
`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?