如何在BigQuery中不使用UNNEST展开数组？GA3查询遇嵌套错误

你遇到的错误是因为hits是数组类型，无法直接访问其嵌套的page.searchKeyword字段。直接用UNNEST展开数组会导致会话级字段（如totals.visits、clientId）重复，增加后续处理的复杂度。以下是两种无需展开整个表即可获取搜索关键词的方案：

方案1：提取会话中的第一个有效搜索词 #

适合每个会话仅对应一个搜索词的场景，通过子查询从hits数组中筛选出第一个非空的搜索关键词：

SELECT 
  PARSE_DATE('%Y%m%d', date) AS date,
  channelGrouping,
  trafficSource.campaign, 
  -- 从当前会话的hits数组中取第一个非空的搜索关键词
  (SELECT page.searchKeyword FROM UNNEST(hits) WHERE page.searchKeyword IS NOT NULL LIMIT 1) AS searchterm,
  SUM(totals.visits) AS sessions, 
  COUNT(DISTINCT clientId) AS users,
  SUM(totals.transactions) AS transactions,
  SUM(totals.totalTransactionRevenue) AS revenue1mil
FROM `vdxl-prod-data-reporting-01.200759185.ga_sessions_*`
WHERE _TABLE_SUFFIX BETWEEN '20230101' AND '20231102'
GROUP BY 1, channelGrouping, trafficSource.campaign, searchterm
ORDER BY 1 DESC

方案2：拼接会话中所有搜索词 #

如果一个会话存在多个搜索词，可将所有有效关键词用逗号拼接成字符串：

SELECT 
  PARSE_DATE('%Y%m%d', date) AS date,
  channelGrouping,
  trafficSource.campaign, 
  -- 把会话中所有非空搜索词拼接成字符串
  ARRAY_TO_STRING(ARRAY(SELECT page.searchKeyword FROM UNNEST(hits) WHERE page.searchKeyword IS NOT NULL), ', ') AS searchterms,
  SUM(totals.visits) AS sessions, 
  COUNT(DISTINCT clientId) AS users,
  SUM(totals.transactions) AS transactions,
  SUM(totals.totalTransactionRevenue) AS revenue1mil
FROM `vdxl-prod-data-reporting-01.200759185.ga_sessions_*`
WHERE _TABLE_SUFFIX BETWEEN '20230101' AND '20231102'
GROUP BY 1, channelGrouping, trafficSource.campaign, searchterms
ORDER BY 1 DESC

原理说明 #

这两种方案都是在SELECT语句中对每个会话的hits数组做局部处理，而非用UNNEST展开整个表，因此不会导致会话级字段重复。子查询会遍历当前会话的hits数组，提取或拼接所需的搜索关键词，最终返回单个值用于分组和展示。

原查询的核心问题是：BigQuery不允许直接从数组类型字段中提取嵌套字段，必须先通过数组函数或子查询处理数组元素。

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