06 May 19 · dan-irving · #bigquery ·   Bookmark   ×

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
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]

Solution:

You will have to unnest the column as shown below:

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

dan-irving

posted on 06 May 19

Enjoy 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