BigQuery-UNNEST中引用的值必须是数组.UNNEST包含STRUCT类型的表达式...在[5:18] [英] BigQuery - Values referenced in UNNEST must be arrays. UNNEST contains expression of type STRUCT ... at [5:18]

问题描述

大家好，我这次遇到了一个新的组数组错误，我将与您共享我正在咨询的表的架构，以便您可以告诉我解决方案.我尝试使用ARRAY_TO_STRING，但在这种情况下无法解决问题...

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

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

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

推荐答案

individual_details 不是数组，而是STRUCT-因此，您不需要UNEST

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

尝试以下

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

这篇关于BigQuery-UNNEST中引用的值必须是数组.UNNEST包含STRUCT类型的表达式...在[5:18]的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！