BigQuery Standard SQL "left" cross join of nested array

user_2573392 0
Viewed 2361

I'm trying to run a query on a tables with records containing nested array. In the example below the nested array is called "ages". Some of the records may have an empty array.

How do I run a query the runs a "left" cross join, so that even a record that has no elements in ages will be output (one row with ages=null)/

 WITH people AS (
 select * from unnest(
 [STRUCT("Tim" as name, [5] as ages),
  STRUCT("Jane" as name, [] as ages)])
)

select 
  name
  from people, unnest(ages) as a
google-bigquery
2 Answers

I've had some issues with this recently and wanted to add another way of looking at it:

WITH people AS (
 SELECT * FROM UNNEST(
 [STRUCT("Tim" AS name, [5] AS ages),
  STRUCT("Jane" AS name, [] AS ages)])
)

SELECT 
  name
FROM people, UNNEST(ages) AS a

is the same as

WITH people AS (
 SELECT * FROM UNNEST(
 [STRUCT("Tim" AS name, [5] AS ages),
  STRUCT("Jane" AS name, [] AS ages)])
)

SELECT 
  name
FROM people
CROSS JOIN UNNEST(ages) AS a

which is the same as

WITH people AS (
 SELECT * FROM UNNEST(
 [STRUCT("Tim" AS name, [5] AS ages),
  STRUCT("Jane" AS name, [] AS ages)])
)

SELECT 
  name
FROM people
LEFT JOIN UNNEST(ages) AS a
WHERE a IS NOT NULL
user_19646920
Related