Solved: BigQuery - Values referenced in UNNEST must be arrays. UNNEST contains expression of type STRUCT

STRUCT is a custom object that can contain a collection of columns that have mixed datatype. Whereas ARRAY is a collection of values of the same datatype. UNNEST Operation can only be performed on ARRAYs and not on STRUCTs, when you do UNNEST on STRUCTs you get the below error:

Values referenced in UNNEST must be arrays. 
UNNEST contains expression of type STRUCT at [5:18]

Example:

Here's an example of STRUCT datatype:

While trying to read data from the table using the below query:

SELECT
    individual_details.gender  AS gender,
    COUNT(DISTINCT profile.owner_id ) AS profile_count_distinct
FROM dataset.profile  AS profile
LEFT JOIN UNNEST(profile.individual_details) as individual_details
GROUP BY 1

Error:
Values referenced in UNNEST must be arrays. 
UNNEST contains expression of type STRUCT at [5:18]

Solution:

individual_details is not an ARRAY, but rather STRUCT, So you do not need UNNEST it, you can directly use it in the select clause, like shown below:

SELECT
    individual_details.gender  AS gender,
    COUNT(DISTINCT profile.owner_id ) AS profile_count_distinct
FROM dataset.profile  AS profile
GROUP BY 1

dan-irving

posted on 27 Mar 19