如何在BigQuery中更新Google Analytics导出表中重复hits字段内的嵌套属性sourcePropertyInfo.sourcePropertyTrackingId

嘿，这个问题我太熟了！BigQuery里处理GA导出的重复数组字段（比如hits）确实容易踩坑，我来给你一步步解决。

问题核心 #

hits是重复数组类型，你不能直接像普通字段那样更新它的嵌套属性sourcePropertyInfo.sourcePropertyTrackingId——必须先把数组拆成单个元素处理，再重新组合成数组赋值回去。

你之前尝试的错误分析 #

• 第一个语句错误：直接访问数组的嵌套字段hits.sourcePropertyInfo，BigQuery不允许对数组类型的字段直接操作嵌套属性，必须先UNNEST拆分数组。
• 第二个语句错误：语法完全不符合UPDATE的规则，不能在SET后面直接写SELECT子句来赋值。
• 第三个语句错误：手动列出所有hit字段不仅繁琐，还可能因为某些嵌套字段本身是重复类型，导致子查询返回多个结果，触发Scalar subquery produced more than one element报错。

正确的UPDATE语句 #

基础版本（假设sourcePropertyInfo不为NULL） #

UPDATE `my_project.my_dataset.ga_sessions_20220801`
SET hits = ARRAY(
  SELECT AS STRUCT
    hit.*,  -- 保留当前hit的所有原有字段，不用手动逐个列出
    -- 重新构造sourcePropertyInfo，替换trackingId，保留其他原有属性
    (SELECT AS STRUCT
      hit.sourcePropertyInfo.* EXCEPT(sourcePropertyTrackingId),
      'your_new_tracking_id' AS sourcePropertyTrackingId
    ) AS sourcePropertyInfo
  FROM UNNEST(hits) hit  -- 将hits数组拆分为单个hit元素
)
WHERE 1=1;  -- 可根据需求修改过滤条件，比如只更新特定日期/特定类型的hits

兼容NULL的版本（如果sourcePropertyInfo可能为NULL） #

如果部分hit的sourcePropertyInfo是NULL，上面的语句会把NULL转换成空STRUCT，所以需要加IFNULL处理：

UPDATE `my_project.my_dataset.ga_sessions_20220801`
SET hits = ARRAY(
  SELECT AS STRUCT
    hit.*,
    IFNULL(
      (SELECT AS STRUCT
        hit.sourcePropertyInfo.* EXCEPT(sourcePropertyTrackingId),
        'your_new_tracking_id' AS sourcePropertyTrackingId
      ),
      hit.sourcePropertyInfo  -- 如果原有sourcePropertyInfo是NULL，保持不变
    ) AS sourcePropertyInfo
  FROM UNNEST(hits) hit
)
WHERE 1=1;

关键原理说明 #

1. 拆分数组：用UNNEST(hits)把数组拆成独立的hit行，这样就能单独处理每个hit的嵌套字段。
2. 保留原有字段：hit.*会自动包含hit的所有属性，避免手动列出几十个字段的麻烦和错误。
3. 修改嵌套STRUCT：用STRUCT.* EXCEPT(字段名)保留sourcePropertyInfo的其他所有属性，只替换需要更新的sourcePropertyTrackingId。
4. 重组数组：最后用ARRAY(...)把处理后的单个hit重新组合成数组，赋值回原表的hits字段。

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