BigQuery访问GA会话数组字段报错，UNNEST使用存疑求助

Got it, let's break down why you're hitting that error and get your query working properly. The core issue here is that in the BigQuery Google Analytics sample dataset, the hits field is an ARRAY of STRUCTs—you can't directly reference hits.item because you're trying to access a field from an entire array instead of individual elements within it. You need to use UNNEST() to "flatten" that array into rows first, and also handle the nested item array inside each hit.

Corrected Query #

SELECT
  item.productSku AS ProductSku,
  (SUM(hit.hitNumber) / COUNT(DISTINCT fullVisitorId)) AS avg_hit_number
FROM
  `bigquery-public-data.google_analytics_sample.ga_sessions_20170801`,
  UNNEST(hits) AS hit,
  UNNEST(hit.item) AS item
WHERE
  item.productSku IS NOT NULL
  AND totals.transactions >= 1
GROUP BY
  item.productSku

Key Fixes & Explanations #

• Unnested nested arrays:
  1. First we use UNNEST(hits) to turn each hit in the array into a separate row (each row now represents one hit from a session).
  2. Then we UNNEST(hit.item) because a single hit can contain multiple products (like items in a checkout cart), so we need to flatten that array too to get individual product SKUs per hit.
• Simplified aggregation: Instead of using two separate subqueries and joining them, we calculate both the sum of hit numbers and count of unique visitors in one pass over the data—this is more efficient and avoids redundant table scans.
• Cleaned up field references: After unnesting, we reference item.productSku and hit.hitNumber directly, which eliminates the array access error you were seeing.

Why Your Original Query Failed #

Your original code tried to reference hits.item.productSku directly without unnesting the hits array. BigQuery doesn't allow accessing a struct field from an entire array—you have to expand the array into individual rows first with UNNEST(). Even if you tried adding UNNEST(hits) before, you might have missed that hit.item is also an array (since a single hit can have multiple products), so unnesting just hits wasn't enough.

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