PostgreSQL 12.8中如何从jsonb数组删除指定对象而非整个数组

你的原语句存在几个关键错误，导致整个数组被清空：

1. 路径错误：jsonb_set的路径'{settings}'无效，因为settings列本身就是数组，并非对象内的settings字段。
2. 数组索引逻辑混乱：settings->'id'对数组直接取id键会返回null，后续减法操作结果也为null，最终导致jsonb_set将数组置空。
3. 未关联当前行：子查询没有与正在更新的行关联，会返回所有匹配id=101的位置值，引发错误的索引删除。

正确的更新语句 #

以下两种方法都可以实现删除数组中id=101的对象：

方法一：展开数组过滤后重新聚合

UPDATE users
SET settings = (
    SELECT jsonb_agg(elem)
    FROM jsonb_array_elements(settings) AS elem
    WHERE elem->>'id' != '101'
)
WHERE settings @> '[{"id": 101}]'; -- 仅更新包含目标元素的行，提升性能

方法二：使用JSON路径查询（更简洁）

UPDATE users
SET settings = jsonb_path_query_array(settings, '$[*] ? (@.id != 101)')
WHERE settings @> '[{"id": 101}]';

说明 #

• jsonb_array_elements将数组展开为单行元素，过滤掉id=101的元素后，用jsonb_agg重新组合成数组。
• jsonb_path_query_array通过JSON路径直接筛选出符合条件的元素，返回新数组。
• WHERE settings @> '[{"id": 101}]'用于跳过不包含目标元素的行，避免无意义的更新操作。

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