PostgreSQL中批量更新jsonb数组内多元素字段的技术问题

在mailing表的recipients列存储着jsonb格式的数组，需要将数组内smsId为1、2、3的元素的status字段从'Sent'更新为'Delivered'。你之前尝试的SQL仅能更新数组中的第一个元素，原因是：当WITH子查询返回多个匹配行时，UPDATE语句对每个mailing_id只会应用一次更新（取FROM子集中的某一行数据），无法批量修改多个元素。

正确的批量更新方法 #

可以通过拆解数组元素→修改符合条件的元素→重新聚合为数组的方式实现批量更新，SQL语句如下：

UPDATE mailing m
SET recipients = (
    SELECT jsonb_agg(
        -- 对匹配smsId的元素，覆盖status字段；其他元素保持不变
        CASE WHEN elem->>'smsId' = ANY('{"1","2","3"}'::text[])
             THEN elem || '{"status": "Delivered"}'::jsonb
             ELSE elem
        END
    )
    FROM jsonb_array_elements(m.recipients) elem
)
-- 仅更新存在需要修改元素的行，避免无意义的更新
WHERE EXISTS (
    SELECT 1
    FROM jsonb_array_elements(m.recipients) elem
    WHERE elem->>'smsId' = ANY('{"1","2","3"}'::text[])
);

代码说明 #

1. jsonb_array_elements(m.recipients)：将目标jsonb数组拆分为单个元素行；
2. CASE语句：判断元素的smsId是否在目标列表中，若是则用||操作符合并新的status字段（会自动覆盖原字段值）；
3. jsonb_agg(...)：将修改后的元素重新聚合为jsonb数组；
4. WHERE EXISTS：过滤出确实有需要修改元素的行，避免对无匹配元素的行执行更新操作，提升效率。

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