Unnesting Multiple Nested Fields Deep in BigQuery

Question

I am trying to use Standard SQL Dialect in BigQuery to unnest the changelog.histories.items repeated record (outlined in green) to access the rows in the nested items table (outlined in blue). The parent Record "changelog" (outlined in Red) is not a repeated record and therefore I am having issues figuring out what to unnest.

Queries that attempt to unnest changelog.histories or changelog.histories.items result in the below error.

SELECT changelog.histories.items.to
FROM jirasparta_database.jira_issues, 
     unnest(changelog.histories) 

Error: Cannot access field items on a value with type ARRAY, ...>, items ARRAYto STRING, field STRING, fieldtype STRING, ...>>, ...>> at [1:28]

Answer 1

#standardSQL
SELECT item.to   
FROM jirasparta_database.jira_issues, 
UNNEST(changelog.histories) history, UNNEST(history.items) item

Basically, you have to flatten the STRUCT and ARRAY values. You can have a look into this documentation for more details.