user912830823 user912830823 -4 years ago 119
SQL Question

How to query multiple nested fields in Bigquery?

When i query two level nested field i miss some rows.

The schema is like this:

Productid STRING REQUIRED
Variants RECORD REPEATED
Variants.SKU STRING NULLABLE
Variants.Size STRING NULLABLE
Variants.Prices RECORD REPEATED
Variants.Prices.Country STRING NULLABLE
Variants.Prices.Currency STRING NULLABLE


Some of Variants.Prices records are empty.

When i query this table with this query:

select Productid,Variants.SKU,Variants.Size
from ga-export-0000.feed.feed_dev
,UNNEST (Variants) AS Variants


i get many more rows than with this one:

select Productid,Variants.SKU,Variants.Size
,Prices.Currency,Prices.Country
from ga-export-0000.feed.feed_dev
,UNNEST (Variants) AS Variants
,UNNEST(Variants.Prices) as Prices


That's because it doesn't returns rows with missing Variants.Prices.

How can i modify my second query so it returns all the rows and if Variants.Prices is missing it shows NULL?

Answer Source

You might be interested in the Flattening arrays topic in the documentation. Instead of a comma, use LEFT JOIN, e.g.:

select Productid,Variants.SKU,Variants.Size
,Prices.Currency,Prices.Country
from `ga-export-0000.feed.feed_dev`
,UNNEST (Variants) AS Variants
LEFT JOIN UNNEST(Variants.Prices) as Prices 

This will return a NULL value of Prices if that array is empty.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download