Unnest multiple columns and join only the sequential values from each column bigquery

Question

I'm facing an issue that I cannot resolve. Is it possible to unnest array so, that each value from each list (column) joins only to its corresponding value (by the sequence) in another list? So first value in the first column only with the first value in the second and the third column and not a cross join. The output after the unnest should be the same as the input (table1). I'm sorry if I wasn't clear enough (so I am appending an example).

Thanks for your help.

with table1 as (
    select 'F1' field, 'm1' m_id, 1 m_unit, 5 m_cost union all
    select 'F1' field,'m2' m_id, 2 m_unit, 3 m_cost  union all
    select 'F1' field, 'm3' m_id, 2 m_unit, 2 m_cost
)

, table2 AS (
    SELECT
        field
        , ARRAY_AGG(m_id IGNORE NULLS) AS m_id
        , ARRAY_AGG(m_unit IGNORE NULLS) AS m_unit
        , ARRAY_AGG(m_cost IGNORE NULLS) AS m_cost
    FROM table1
    GROUP BY 1
)

SELECT * EXCEPT (m_id, m_unit, m_cost)
FROM table2,
UNNEST (m_id) as m_id_unnested,
UNNEST (m_unit) as m_unit_unnested,
UNNEST (m_cost) as m_cost_unnested

Desired output should be the same as the result of table1:

The result I get from unnesting is a cross join between all nested values:

Adding solution to my problem:

with table1 as (
    select 'F1' field, 'm1' m_id, 1 m_unit, 5 m_cost union all
    select 'F1' field,'m2' m_id, 2 m_unit, 3 m_cost  union all
    select 'F1' field, 'm3' m_id, 2 m_unit, 2 m_cost
)

, table2 AS (
    SELECT
        field
        , ARRAY_AGG(m_id IGNORE NULLS) AS m_id
        , ARRAY_AGG(m_unit IGNORE NULLS) AS m_unit
        , ARRAY_AGG(m_cost IGNORE NULLS) AS m_cost
    FROM table1
    GROUP BY 1
)

SELECT field, m_id_unnested, m_unit_unnested, m_cost_unnested
FROM table2 LEFT JOIN
UNNEST(m_id) m_id_unnested WITH OFFSET pos1
ON 1=1 LEFT JOIN
UNNEST(m_unit) m_unit_unnested WITH OFFSET pos2
ON pos1 = pos2 LEFT JOIN
UNNEST(m_cost) m_cost_unnested WITH OFFSET pos3
ON pos1 = pos3
;

score 2 · Accepted Answer · answered Jul 14 '22 at 09:37

You can use WITH OFFSET along with UNNEST and join flattend arrays with offsets. Consider below query.

https://cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax#unnest_and_with_offset

SELECT * EXCEPT (m_id, m_unit, m_cost, o1, o2, o3)
  FROM table2,
       UNNEST (m_id) as m_id_unnested WITH OFFSET o1,
       UNNEST (m_unit) as m_unit_unnested WITH OFFSET o2,
       UNNEST (m_cost) as m_cost_unnested WITH OFFSET o3
 WHERE o1 = o2 AND o1 = o3;

or,

SELECT * EXCEPT (m_id, m_unit, m_cost, offset)
  FROM table2,
       UNNEST (m_id) as m_id_unnested WITH OFFSET
  JOIN UNNEST (m_unit) as m_unit_unnested WITH OFFSET USING(offset)
  JOIN UNNEST (m_cost) as m_cost_unnested WITH OFFSET USING(offset);

I already found the solution, but yours is more elegant! Thanks a lot! — KayEss, Jul 14 '22 at 09:42

Unnest multiple columns and join only the sequential values from each column bigquery

1 Answers1