Koba Koba - 1 year ago 76
SQL Question

Join a table with a repeated field using UNNEST()

I am trying to join two tables, one with a repeated field, using the Standard SQL in BigQuery. Using Legacy SQL I came up with this query

Legacy SQL:

SELECT
b.*,
t.field1,
t.field2
FROM
FLATTEN([table1],repeated_field) AS b
LEFT JOIN
[table2] AS t
ON
b.Row = t.RowLabel
b.seat = t.SeatLabel


The repeated field is the
seat
. I tried using
unnest()
and looking at the migration guide, but could not come up with a query myself. Help appreciated thanks.

Answer Source

below is for BigQuery Standard SQL

#standardSQL
SELECT   
  b.*,
  t.field1,
  t.field2
FROM `table1` AS b, UNNEST(Seats) AS Seat
JOIN `table2` AS t
ON b.Row = t.RowLabel
AND Seat = t.SeatLabel  

You can test it with dummy data as below

#standardSQL
WITH `table1` AS (
  SELECT '1' AS Row, ['a', 'b', 'c'] AS Seats
),
`table2` AS (
  SELECT '1' AS RowLabel, 'b' AS SeatLabel, 111 AS field1, 222 AS field2 UNION ALL
  SELECT '1' AS RowLabel, 'a' AS SeatLabel, 111 AS field1, 222 AS field2 UNION ALL
  SELECT '1' AS RowLabel, 'd' AS SeatLabel, 111 AS field1, 222 AS field2
)
SELECT   
  b.*,
  t.field1,
  t.field2
FROM `table1` AS b, UNNEST(Seats) AS Seat
JOIN `table2` AS t
ON b.Row = t.RowLabel
AND Seat = t.SeatLabel
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download