查询BigQuery GA会话中customDimensions总数报错如何解决

报错翻译 #

Cannot access field customDimensions on a value with type ARRAY<STRUCT<productSKU STRING, v2ProductName STRING, v2ProductCategory STRING, ...>> at [5:21]

翻译为：无法在ARRAY<STRUCT<productSKU STRING, v2ProductName STRING, v2ProductCategory STRING, ...>>类型的数组值上直接访问customDimensions字段，错误位于SQL语句第5行第21个字符处。

根因说明 #

• 执行UNNEST(hits.product) AS prod后，hits.product依然是原生的数组类型，不能直接调用数组内结构体的customDimensions字段，应该使用展开后的单条产品结构体prod调用该字段
• 统计计数时无需从最顶层hits开始嵌套引用，直接使用customDimensions展开后的别名cust取值即可

修复后SQL #

仅统计有customDimensions的记录 #

SELECT COUNT(cust.value)
FROM `bigquery-public-data.google_analytics_sample.ga_sessions_xxxx`,
UNNEST(hits) AS hits,
UNNEST(hits.product) AS prod,
UNNEST(prod.customDimensions) AS cust

包含无customDimensions的产品的统计（无值时计数为0） #

SELECT COUNT(cust.value)
FROM `bigquery-public-data.google_analytics_sample.ga_sessions_xxxx`,
UNNEST(hits) AS hits,
UNNEST(hits.product) AS prod
LEFT JOIN UNNEST(prod.customDimensions) AS cust

内容的提问来源于stack exchange，提问作者Daniel