postgresql - 更新 Postgres 中的 JSON 列？

Question

我在 Postgres 中有一个 JSON 列，其中包含以下结构中的数据

{
    "listings": [{
            "id": "KTyneMdrAhAEKyC9Aylf",
            "active": true
        },
        {
            "id": "ZcjK9M4tuwhWWdK8WcfX",
            "active": false
        }
    ]
}

我需要做一些事情，所有这些我都不确定该怎么做

向列表数组中添加一个新对象

根据 id

从列表数组中删除一个对象

根据对象的 id 更新列表数组中的对象

如果有任何内置函数可以执行此操作，我也在使用 Sequelize(我看不到任何明显的东西)

Answer 1

demo:db<>fiddle

插入( jsonb_insert() ):

UPDATE mytable
SET mydata = jsonb_insert(mydata, '{listings, 0}', '{"id":"foo", "active":true}');

更新(扩展数组，使用 jsonb_set() 更改值，重新聚合):

UPDATE mytable
SET mydata = jsonb_set(mydata, '{listings}', s.a)
FROM
(
    SELECT
        jsonb_agg(
            CASE WHEN elems ->> 'id' = 'foo' THEN jsonb_set(elems, '{active}', 'false')
                ELSE elems
            END
        ) as a
    FROM
        mytable,
        jsonb_array_elements(mydata -> 'listings') AS elems
) s;

删除(展开数组，过滤相关元素，重新聚合):

UPDATE mytable
SET mydata = jsonb_set(mydata, '{listings}', s.a)
FROM
(
    SELECT
        jsonb_agg(elems) as a
    FROM
        mytable,
        jsonb_array_elements(mydata -> 'listings') AS elems
    WHERE elems ->> 'id' != 'foo'
) s;

更新和删除可以像插入一样在一行中完成。但在这种情况下，您必须使用数组元素索引而不是某个值。如果要使用值更改数组元素，则需要先读取它。这只能通过预先扩展来实现。