BigQuery(GA4数据)中嵌套数组跨表更新的SQL实现

场景说明 #

需要更新table1中event_params数组里，key为country且value.string_value为US的元素，将其替换为table2中映射的NL值。以下是适配支持数组/结构体的SQL引擎（如BigQuery、PostgreSQL）的解决方案：

核心更新语句 #

UPDATE table1
SET event_params = ARRAY(
  SELECT AS STRUCT
    ep.key,
    CASE
      WHEN ep.key = 'country' AND ep.value.string_value = t2.country
      THEN STRUCT(t2.new_country AS string_value)
      ELSE ep.value
    END AS value
  FROM UNNEST(table1.event_params) ep
  LEFT JOIN table2 t2 
    ON ep.key = 'country' AND ep.value.string_value = t2.country
)
WHERE EXISTS (
  SELECT 1
  FROM UNNEST(table1.event_params) ep
  JOIN table2 t2 
    ON ep.key = 'country' AND ep.value.string_value = t2.country
)

语句解析 #

1. 数组展开与重构：

   • 用UNNEST将event_params数组拆分为单行记录，逐个处理每个数组元素
   • 用ARRAY()+子查询重新构造数组，确保仅修改目标元素，其他元素保持原样

2. 条件替换逻辑：

   • CASE判断：仅当元素的key是country，且当前string_value存在于table2的映射关系中时，替换value结构体的string_value字段为new_country
   • 非目标元素直接保留原value结构体

3. 过滤不必要更新：

   • WHERE EXISTS只筛选出确实有需要更新元素的行，避免对无匹配数据的行执行无效更新

适配不同SQL引擎的调整 #

• PostgreSQL：如果value是自定义类型，需用ROW(t2.new_country)::your_value_type替代STRUCT(...)，例如：

  THEN ROW(t2.new_country)::value_struct AS value
  

• 确保映射唯一性：若table2中同一country存在多条映射，需先通过DISTINCT或聚合函数（如MAX()）保证映射值唯一，避免出现多匹配问题

预览更新结果（测试用） #

执行更新前，可先运行以下语句确认修改后的结果是否符合预期：

SELECT
  *,
  ARRAY(
    SELECT AS STRUCT
      ep.key,
      CASE
        WHEN ep.key = 'country' AND ep.value.string_value = t2.country
        THEN STRUCT(t2.new_country AS string_value)
        ELSE ep.value
      END AS value
    FROM UNNEST(table1.event_params) ep
    LEFT JOIN table2 t2 
      ON ep.key = 'country' AND ep.value.string_value = t2.country
  ) AS updated_event_params
FROM table1
WHERE EXISTS (
  SELECT 1
  FROM UNNEST(table1.event_params) ep
  JOIN table2 t2 
    ON ep.key = 'country' AND ep.value.string_value = t2.country
)

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