arrays - BigQuery - UNNEST 中引用的值必须是数组。 UNNEST 在 [5 :18] 处包含 STRUCT ... 类型的表达式

Question

大家好，这次我遇到了一个新的数组分组错误，我将与您分享我正在咨询的表的架构，以便您告诉我解决方案。我尝试使用 ARRAY_TO_STRING 但在这种情况下它没有成功......

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]

Answer 1

individual_details不是数组，而是结构 - 所以你不需要 UNNEST 它

试试下面

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