Sql 从 JSON 数组中移除对象

发布于03月15日

从我的PostgreSQL12.8数据库的表行中，我试图从数组中删除一个对象，但它却从表中删除了整个对象array.

settings列包含以下对象数组:

    [
        {
            "id": 100,
            "name": "testOne",
            "settings": "settingOne"
        },
        {
            "id": 101,
            "name": testTwo,
            "settings": "settingTwo"
        },
]

我在users表中有三行，其中列settings的类型为jsonb，其中包含一个对象array.

我想为所有用户删除id=101的对象.我try 了以下查询:

update users
set settings = 
    jsonb_set(settings , '{settings}', (settings->'id') - (select distinct position-1 from users, jsonb_array_elements(settings) 
    with ordinality arr(elem, position) WHERE elem->>'id' = '101')::int)

通过执行上述查询，它将从设置中删除所有内容.如何修改上述查询才能达到下面的结果？

   [
            {
                "id": 100,
                "name": "testOne"
                "settings": "settingOne"
            }
    ]

推荐答案

这应该可以做到:

UPDATE users u
SET    settings = (SELECT jsonb_agg(a.elem)
                   FROM   jsonb_array_elements(u.settings) AS a(elem)
                   WHERE  (a.elem ->> 'id' = '101') IS NOT TRUE)
WHERE  u.settings @> '[{"id":101}]';

fiddle个

添加的OUTER WHERE子句确保只更新实际更改的行.您可能有额外的过滤器...

这将从具有"id": all个对象的数组中删除all个对象.(可能不止一个.)

要仅删除第一个匹配，请执行以下操作:

UPDATE users u
SET    settings = u.settings
                - (SELECT a.ord::int-1
                   FROM   jsonb_array_elements(u.settings) WITH ORDINALITY a(elem, ord)
                   WHERE  a.elem ->> 'id' = '101'
                   LIMIT  1)
WHERE  u.settings @> '[{"id":101}]';

fiddle个

值得注意的是，如果在相关子查询中重复使用表名，这将是代价高昂的inflating --并且需要额外的过滤器来链接回更新后的表.

之所以有-1，是因为Postgres序号以1开头，而JSON数组元素以0开头.

约WITH ORDINALITY人: