如何有条件更新Postgres JSONB列中的数组值

针对你的PROPS表结构和需求，以下是可以实现批量更新的SQL语句：

UPDATE PROPS
SET data = (
  SELECT jsonb_agg(
    CASE
      WHEN elem->>'refKey' = '123' THEN elem || '{"refKey": 125}'::jsonb
      ELSE elem
    END
  )
  FROM jsonb_array_elements(data) AS elem
);

语句说明： #

• jsonb_array_elements(data)：将data列的jsonb数组拆分为单个对象行，每个行对应数组中的一个元素elem
• CASE逻辑：检查每个元素的refKey值（->>操作符返回文本类型，所以和字符串'123'比较），如果匹配则用||操作符将新的refKey:125合并到原对象中（会覆盖原有refKey字段），不匹配则保留原元素
• jsonb_agg()：将修改后的所有元素重新聚合为jsonb数组，赋值回data列

优化建议： #

如果表数据量大，建议添加WHERE条件只更新包含目标值的行，避免无意义的全表更新：

UPDATE PROPS
SET data = (
  SELECT jsonb_agg(
    CASE
      WHEN elem->>'refKey' = '123' THEN elem || '{"refKey": 125}'::jsonb
      ELSE elem
    END
  )
  FROM jsonb_array_elements(data) AS elem
)
WHERE data @> '[{"refKey": 123}]'::jsonb;

特殊情况处理： #

如果data列可能存在null或空数组，可使用COALESCE确保更新后仍保留空数组而非null：

UPDATE PROPS
SET data = COALESCE(
  (
    SELECT jsonb_agg(
      CASE
        WHEN elem->>'refKey' = '123' THEN elem || '{"refKey": 125}'::jsonb
        ELSE elem
      END
    )
    FROM jsonb_array_elements(data) AS elem
  ),
  '[]'::jsonb
);

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