Postgres中根据ID替换jsonb数组内的指定对象

因为你的场景中ID是唯一的，我们可以通过展开JSONB数组、替换匹配元素、重新聚合数组的方式实现需求，具体方案如下：

直接更新表的SQL语句 #

假设你的表名为target_table，JSONB列名为data_column，传入的新对象为new_obj（JSONB类型），执行以下UPDATE语句即可完成替换：

UPDATE target_table
SET data_column = (
  -- 展开数组后逐个判断，匹配ID则替换为新对象，否则保留原元素，最后重新聚合为数组
  SELECT jsonb_agg(
    CASE WHEN elem->>'id' = new_obj->>'id' THEN new_obj ELSE elem END
  )
  FROM jsonb_array_elements(data_column) AS elem
)
-- 仅更新存在匹配ID的行，避免无意义操作
WHERE EXISTS (
  SELECT 1
  FROM jsonb_array_elements(data_column) AS elem
  WHERE elem->>'id' = new_obj->>'id'
);

封装成可复用函数 #

如果需要多次调用，建议封装成PL/pgSQL函数：

CREATE OR REPLACE FUNCTION replace_jsonb_array_item(
  original_arr jsonb,
  new_item jsonb
) RETURNS jsonb AS $$
BEGIN
  RETURN (
    SELECT jsonb_agg(
      CASE WHEN elem->>'id' = new_item->>'id' THEN new_item ELSE elem END
    )
    FROM jsonb_array_elements(original_arr) AS elem
  );
END;
$$ LANGUAGE plpgsql;

调用函数更新的示例：

UPDATE target_table
SET data_column = replace_jsonb_array_item(
  data_column,
  '{"id": "1231-213-331", "name": "New Name For Object One"}'::jsonb
)
WHERE EXISTS (
  SELECT 1
  FROM jsonb_array_elements(data_column) AS elem
  WHERE elem->>'id' = '1231-213-331'
);

补充说明 #

• 你之前尝试的||操作符是拼接数组，会在原数组末尾新增元素而非替换，不符合需求；
• jsonb_set需要明确知道元素在数组中的索引，而你无法直接通过ID获取索引，因此展开聚合的方式更适配当前场景。

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