sql - 嵌套hits.product.customDimensions错误

Question

我在查询hits.product.customDimensions时遇到问题（hits.customDimensions上的相同逻辑工作正确）。我不明白为什么附加的嵌套会导致数组错误。任何帮助表示赞赏。谢谢！

标准SQL

SELECT
  fullVisitorId,
  visitId,
  hits.hitNumber,
  product.productSKU,
  MAX(IF(c.index=1,c.value, null)) AS customDimesion1
FROM 17823880.ga_sessions_20180128,
UNNEST(hits) AS hits, 
UNNEST(hits.product) as product,
UNNEST(hits.product.customDimensions) as c
GROUP BY 1, 2, 3, 4

错误：


无法访问具有类型的值的字段customDimensions
[11:23]处的阵列>


标准SQL-此查询在下面正常工作

SELECT
  fullVisitorId,
  visitId,
  hits.hitNumber,
  MAX(IF(c.index=1,c.value, null)) AS customDimesion1
FROM 17823880.ga_sessions_20180128,
UNNEST(hits) AS hits,
UNNEST(hits.customDimensions) as c
GROUP BY 1, 2, 3

Answer 1

问题是您为product中的元素赋予了UNNEST(hits.product)别名，但是在后续的UNNEST(hits.product.customDimensions)中没有引用该别名，因此最终使用了原始的product数组。取消嵌套后的元素尝试以下方法：

SELECT
fullVisitorId,
visitId,
hits.hitNumber,
product.productSKU,
MAX(IF(c.index=1,c.value, null)) AS customDimesion1
FROM 17823880.ga_sessions_20180128,
UNNEST(hits) AS hits, 
UNNEST(hits.product) as product,
UNNEST(product.customDimensions) as c
GROUP BY 1, 2, 3, 4