如何利用其他表数据更新BigQuery嵌套数组字段？

我帮你调整这个嵌套字段更新的SQL语句，让它能正确关联另一张表的值来完成目标表的嵌套字段更新——你的原方案核心问题是缺少了关联属性表的逻辑，所以无法获取到要替换的map.category值。

完整修正后的SQL #

#standardSQL
UPDATE `attribution.daily_sessions_20180301_copy1` AS target
SET hits = ARRAY(
  SELECT AS STRUCT hit.* REPLACE(
    ARRAY(
      SELECT AS STRUCT prod.* REPLACE(map.category AS productCategoryAttribute)
      FROM UNNEST(hit.product) AS prod
      -- 这里替换成你实际的关联条件，比如产品ID、SKU等
      LEFT JOIN `attribution.attribute_category_map` AS map
      ON prod.productId = map.productId
    ) AS product
  )
  FROM UNNEST(target.hits) AS hit
)
WHERE EXISTS (
  -- 可选：仅更新存在匹配属性的记录，减少无效更新
  SELECT 1
  FROM UNNEST(target.hits) AS hit
  CROSS JOIN UNNEST(hit.product) AS prod
  JOIN `attribution.attribute_category_map` AS map
  ON prod.productId = map.productId
);

关键调整说明 #

• 关联属性表：通过LEFT JOIN把嵌套的product数组和属性表关联，这样才能拿到map.category的值来替换productCategoryAttribute。如果不需要保留没有匹配属性的产品记录，可以把LEFT JOIN改成INNER JOIN。
• 避免无效更新：WHERE EXISTS子句会过滤掉没有任何匹配产品的记录，只对有需要更新的记录执行操作，提升查询性能。
• 关联条件适配：一定要根据你的实际业务场景修改ON后面的关联键（比如prod.sku = map.sku或者prod.productName = map.productName），确保能正确匹配到对应的属性值。

测试验证方法 #

在执行UPDATE之前，建议先改成SELECT语句验证结果是否符合预期，避免误更新数据：

#standardSQL
SELECT target.* REPLACE(
  ARRAY(
    SELECT AS STRUCT hit.* REPLACE(
      ARRAY(
        SELECT AS STRUCT prod.* REPLACE(map.category AS productCategoryAttribute)
        FROM UNNEST(hit.product) AS prod
        LEFT JOIN `attribution.attribute_category_map` AS map
        ON prod.productId = map.productId
      ) AS product
    )
    FROM UNNEST(target.hits) AS hit
  ) AS hits
)
FROM `attribution.daily_sessions_20180301_copy1` AS target
LIMIT 10;

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