Unnest array on bigQuery
14:20 03 Nov 2022

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?

sql google-bigquery