Menu
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]
402
BigQuery automatically flattens nested fields when querying. To query a column with nested data, each field must be identified in the context of the column that contains it. For example: customer.id refers to the id field in the customer column.
Can We Unnest Multiple Arrays? When we use the UNNEST function on a column in BigQuery, all the rows under that column is flattened all at once. Currently, the UNNEST function does not accept multiple arrays as parameters. We need to merge the arrays into a single array before flattening it.
To convert an ARRAY into a set of rows, also known as "flattening," use the UNNEST operator. UNNEST takes an ARRAY and returns a table with a single row for each element in the ARRAY . Because UNNEST destroys the order of the ARRAY elements, you may wish to restore order to the table.
#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.
54
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
