CODEWITHSUNDEEP
google-bigquery
Lior
Lior

Reputation: 1467

BigQuery Standard SQL "left" cross join of nested array

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

Upvotes: 5

Views: 2927

Answers (2)

Korean_Of_the_Mountain
Korean_Of_the_Mountain

Reputation: 1577

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

Upvotes: 0

Mikhail Berlyant
Mikhail Berlyant

Reputation: 173151

#standardSQL
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

Upvotes: 7

Related Questions