PostgreSQL中如何更新jsonb列中的数组对象

原语句的问题 #

• WHERE条件无效：contacts->>'messageId'='1'无法匹配目标行。contacts是jsonb数组，直接用->>提取messageId会尝试从数组本身获取该字段，但数组没有这个属性。正确的匹配逻辑应该是检查数组中是否存在messageId为'1'的对象。
• 硬编码数组索引：原语句固定更新索引为1的元素，但目标对象可能在数组的任意位置，硬编码索引会导致更新错误的元素。
• 字段更新方式冗余：两次嵌套jsonb_set再合并的写法可以简化，直接用||操作符将更新字段合并到原对象即可。

正确的更新语句 #

以下语句会遍历数组中的每个元素，精准找到messageId为'1'的对象并更新指定字段，其他元素保持不变：

UPDATE "Disparos" d
SET contacts = (
  SELECT jsonb_agg(
    CASE WHEN elem->>'messageId' = '1'
         THEN elem || '{"ack": "1", "timestamp": 1705341218404}'::jsonb
         ELSE elem
    END
  )
  FROM jsonb_array_elements(d.contacts) elem
)
WHERE d.contacts @> '[{"messageId": "1"}]'::jsonb;

语句说明 #

1. 筛选目标行：contacts @> '[{"messageId": "1"}]'::jsonb用于筛选出contacts数组中包含messageId为'1'的对象的行，避免对无匹配数据的行执行更新操作。
2. 重构数组：
   • jsonb_array_elements(d.contacts)将原数组拆分为单个jsonb对象。
   • 通过CASE判断每个对象的messageId，符合条件的对象用||操作符合并更新字段（ack设为"1"，timestamp设为1705341218404），不符合条件的保留原对象。
   • jsonb_agg将处理后的所有对象重新组装为jsonb数组，替换原contacts字段。

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