如何用单条PostgreSQL语句移除JSONB数组中的指定字符串集合？

直接用这条UPDATE语句就能完成需求，完美匹配你的三个限制：

UPDATE users
SET favorites = jsonb_set(
    favorites,
    '{shapes}',
    (
        SELECT jsonb_agg(elem)
        FROM jsonb_array_elements(favorites->'shapes') AS elem
        WHERE elem::text NOT IN ('"Square"', '"Circle"')
    )
);

关键细节说明： #

• 满足限制1：如果待移除的形状在某行的shapes里不存在，WHERE elem::text NOT IN (...)会自动跳过这些不存在的元素，只移除实际存在的项，不会影响其他元素。
• 满足限制2：jsonb_set只会修改favorites里的shapes字段，对象中其他所有字段都会原封不动保留，完全不需要把整个JSONB对象转成数组。
• 满足限制3：这就是单条查询，执行一次就能完成所有用户数据的更新。

动态移除列表的扩展写法 #

如果你的待移除形状列表是可变的（比如从变量传入），可以用<> ALL替代NOT IN，更灵活：

-- 假设待移除的形状列表是一个PostgreSQL文本数组
WITH to_remove AS (
    SELECT ARRAY['"Square"', '"Circle"']::text[] AS shapes
)
UPDATE users
SET favorites = jsonb_set(
    favorites,
    '{shapes}',
    (
        SELECT jsonb_agg(elem)
        FROM jsonb_array_elements(favorites->'shapes') AS elem, to_remove
        WHERE elem::text <> ALL(to_remove.shapes)
    )
);

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