Couchbase使用MERGE查询为客户字段追加订单数组并删除冗余属性

实现方案 #

需求梳理 #

• 为Customer实体新增orders属性，数据类型为array<Order>
• 迁移Customer关联的所有订单数据到该属性中
• 迁移完成后删除orders.personId冗余字段

现有语句问题 #

你目前编写的MERGE语句存在两处核心问题：

1. 每次匹配订单时会直接覆盖cust.orders，而非追加订单到数组，最终每个客户仅会保留最后一条匹配的订单
2. 重复设置匹配条件，ON子句已经声明了关联逻辑，UPDATE后的WHERE条件多余，还存在personId大小写不一致的笔误

修正后的操作步骤 #

1. 新增orders字段（以BigQuery语法为例，其他数据库可对应调整类型定义）

ALTER TABLE `Customers` ADD COLUMN IF NOT EXISTS orders ARRAY<STRUCT<order_id INT64, person_id INT64, total_price FLOAT64>> DEFAULT [];

2. 执行订单数据迁移

MERGE INTO `Customers` AS cust
USING (
  -- 按客户ID聚合所有关联订单，一次性生成完整的订单数组
  SELECT 
    personId,
    ARRAY_AGG(STRUCT(id AS order_id, person_id, total_price)) AS aggregated_orders
  FROM `Orders`
  GROUP BY personId
) AS ord 
ON cust.id = ord.personId
WHEN MATCHED THEN
  UPDATE SET cust.orders = ord.aggregated_orders;

3. 确认数据无误后删除冗余字段

ALTER TABLE `Orders` DROP COLUMN IF EXISTS personId;

注意事项 #

操作前务必先备份Customers和Orders两张表的数据，避免误操作导致数据丢失。
若使用非BigQuery数据库，可对应调整数组语法：比如PostgreSQL可使用jsonb[]存储订单数组，聚合函数替换为jsonb_agg即可。

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