Menu
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
475
BigQuery automatically flattens nested fields when querying. To query a column with nested data, each field must be identified in the context of the column that contains it. For example: customer.id refers to the id field in the customer column.
Querying nested arrays If a table contains an ARRAY of STRUCT s, you can flatten the ARRAY to query the fields of the STRUCT . You can also flatten ARRAY type fields of STRUCT values.
BigQuery supports ANSI SQL join types. JOIN operations are performed on two items based on join conditions and join type.
Cross joins (Cartesian product) Cross joins are queries where each row from the first table is joined to every row in the second table (there are non-unique keys on both sides). The worst case output is the number of rows in the left table multiplied by the number of rows in the right table.
#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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
