How to UNNEST multiple arrays in BigQuery standardSQL

0
votes

I am selecting data from Google Bigquery table which includes JSON column. My table has multiple nested arrays, one of the includes two nested levels. here is my table schema

https://imgur.com/UBPKUMx

My statement is:

SELECT 
items.*,
pay.*,
credits.creditnoteid,
credits.id,
credits.total
FROM client_account.invoices,
UNNEST(lineitems) items,
UNNEST(items.tracking),
UNNEST(payments) pay,
UNNEST(creditnotes) credits

https://imgur.com/c1YT258

Unfortunately I get no results... Can you help me to unnest all of the arrays.

enter image description here

1
sqlgoogle-bigqueryunnest
I do not think it is related but why are you unnesting items.tracking? You do not use it in any part of your query. – Alessandro
Hi, if I do not unnest items.tracking I can not get all items.* columns. items.tracking will remain JSON format. – UDO
Can you share the table schema? It is very useful if you provide more details possible to reproduce the issue. Check out how to effectively ask questions here. stackoverflow.com/help/how-to-ask – Alessandro
sure - imgur.com/UBPKUMx – UDO
SELECT items.*, tracking.*, pay.*, credits.creditnoteid, credits.id, credits.total FROM client_account.invoices, UNNEST(lineitems) items, UNNEST(items.tracking) tracking, UNNEST(payments) pay, UNNEST(creditnotes) credits same - no results When I exclude UNNEST(creditnotes) credits and use this statemnt, it works SELECT items.*, tracking.*, pay.* FROM client_account.invoices, UNNEST(lineitems) items, UNNEST(items.tracking) tracking, UNNEST(payments) pay – UDO

1 Answers

1
votes

Ok, I did a test on one of my datasets. I think that creditnotes is always null. Because in my case I get no results when I unnest a column that is always null. You can fix it by using LEFT JOIN I modified your query to use left joins but you might be able to tune it better.

SELECT 
items.*,
tracking.*,
pay.*,
credits.creditnoteid,
credits.id,
credits.total
FROM client_account.invoices
LEFT JOIN UNNEST(lineitems) items
LEFT JOIN UNNEST(items.tracking) tracking
LEFT JOIN UNNEST(payments) pay
LEFT JOIN UNNEST(creditnotes) credits