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

user_11504069 0
Viewed 15391

Hello guys this time I came across a new error to group array, I will share with you the schema of the table I am consulting so you can tell me the solution. I tried to use ARRAY_TO_STRING but in this case it didn't work out ...

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
ORDER BY 2 DESC

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

Schema table

google-bigquery arrays
1 Answers

individual_details is not an ARRAY, but rather STRUCT - so you do not need UNNEST it

Try below 

SELECT
    individual_details.gender  AS gender,
    COUNT(DISTINCT profile.owner_id ) AS profile_count_distinct
FROM dataset.profile  AS profile
GROUP BY 1
ORDER BY 2 DESC
user_52219440
Related