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

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

在UNNEST中引用的值必须是数组。在[5:18]处，UNNEST包含了结构类型的表达式。

- Felipe FB

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

- Mikhail Berlyant