如何通过BigQuery的MERGE策略向现有数组追加新元素？

需求说明 #

通过MERGE语句，将源表中的数组元素追加到目标表对应记录的数组中，最终目标表记录的associated_customers数组为['vlad', 'katya', 'calin']。

完整MERGE语句 #

MERGE `XXX.temp.associated_lucas_test` AS target_table
USING (
  SELECT * FROM `XXX.temp.associated_lucas_source`
) source_table
-- 匹配条件：用唯一标识字段确定同一条记录
ON target_table.global_entity_id = source_table.global_entity_id 
   AND target_table.customer_id = source_table.customer_id
-- 匹配到记录时：合并数组并去重
WHEN MATCHED THEN
  UPDATE SET
    associated_customers = ARRAY_UNIQUE(ARRAY_CONCAT(target_table.associated_customers, source_table.associated_customers))
-- 未匹配到记录时：插入新记录（可选，根据业务需求调整）
WHEN NOT MATCHED THEN
  INSERT (global_entity_id, customer_id, associated_customers)
  VALUES (source_table.global_entity_id, source_table.customer_id, source_table.associated_customers);

关键逻辑拆解 #

1. ON条件设置
   选择global_entity_id和customer_id作为匹配键，这两个字段的组合唯一标识一条用户实体记录，确保仅更新目标表中对应的同一条记录。

2. 匹配时的更新逻辑

   • ARRAY_CONCAT(target_table.associated_customers, source_table.associated_customers)：将目标表原有数组与源表新数组合并，直接得到包含新元素的数组。
   • ARRAY_UNIQUE(...)：可选但推荐使用，自动去除合并后数组中的重复元素，避免同一值被多次追加。如果业务允许重复元素，可直接去掉这个函数，仅保留ARRAY_CONCAT。

3. 未匹配时的插入逻辑
   如果源表存在目标表没有的新记录（即global_entity_id + customer_id组合未在目标表出现），则将该记录插入目标表，保证数据的完整性。

验证结果 #

执行MERGE后，查询目标表：

SELECT * FROM `XXX.temp.associated_lucas_test`;

返回结果符合预期：

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