How to query multiple nested fields in Bigquery?

Question

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?

You have a low rate. Important on SO, you have to mark accepted answers by using the tick on the left of the posted answer, below the voting. This will increase your rate. See how this works by visinting this link: meta.stackoverflow.com/questions/5234/… — Pentium10
Thanks @Pentium10, just went through all my questions and marked accepted answers. — user912830823

Elliott Brossard Elliott Brossard · Accepted Answer · 2017-03-23T13:24:42

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.

How to query multiple nested fields in Bigquery?

1 Answers