PostgreSQL中如何正确移除JSONB数组内的指定元素

我使用( fields -> ?1 ) - string_to_array( ?2, ',' )函数想要移除JSONB数组中存在于第二个数组的元素，但该函数却把原数组中没有的元素添加到了JSONB里。

我的JSONB字段示例：

{
"23": [ "aaa", "bbb", "ccc" ]
}

当传入第二个数组[ "aaa", "jjj", "ddd" ]时，当前返回结果错误地添加了无关元素：

{
"23": [ "bbb", "ccc", "ddd", "jjj" ]
}

期望返回结果仅移除重合元素aaa：

{
"23": [ "bbb", "ccc" ]
}

我尝试过的更新语句：

update mytable
set fields = 
    jsonb_set(fields, '{23}', (fields->'23') - [ 'aaa', 'bbb' ] );

PostgreSQL中jsonb数组 - text数组的操作并非求差集，这种用法会导致意外的元素添加，不符合需求。正确的做法是先展开JSONB数组，过滤掉需要移除的元素，再重新聚合为JSONB数组。

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

UPDATE mytable
SET fields = jsonb_set(
    fields,
    '{23}',
    (SELECT jsonb_agg(elem)
     FROM jsonb_array_elements_text(fields->'23') elem
     WHERE elem <> ALL(string_to_array('aaa,jjj,ddd', ',')))
);

方法二：使用数组差集操作 #

UPDATE mytable
SET fields = jsonb_set(
    fields,
    '{23}',
    to_jsonb(
        array(
            SELECT unnest(jsonb_array_elements_text(fields->'23')) 
            EXCEPT 
            SELECT unnest(string_to_array('aaa,jjj,ddd', ','))
        )
    )
);

这两种方式都会只保留原数组中不在移除列表里的元素，完全符合需求。

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