Unnest Multiple level nested fields in BigQuery
Bigquery supports denormalized data with repeated structures. To access the table with nested structures use UNNEST command.
Example: Here's a sample table
Here is an example query that attempt to unnest changelog.histories or changelog.histories.items result in the below error.
SELECT changelog.histories.items.to\nFROM jirasparta_database.jira_issues, \n unnest(changelog.histories) \n
\n\nError: Cannot access field items on a value with type ARRAY, ...>, \nitems ARRAYto STRING, field STRING, fieldtype STRING, ...>>, ...>> at [1:28]
Solution:
You will have to unnest the column as shown below:
SELECT item.to \nFROM jirasparta_database.jira_issues, \nUNNEST(changelog.histories) history, UNNEST(history.items) item
dan-irving
posted onEnjoy great content like this and a lot more !
Signup for a free account to write a post / comment / upvote posts. Its simple and takes less than 5 seconds
Post Comment