arrays - 更新列表 Postgres jsonb 中的值

Question

我正在尝试更新 json

[{"id": "1", "name": "myconf", "icons": "small", "theme": "light", "textsize": "large"},
 {"id": 2, "name": "myconf2", "theme": "dark"}, {"name": "firstconf", "theme": "dark", "textsize": "large"},
 {"id": 3, "name": "firstconxsf", "theme": "dassrk", "textsize": "lassrge"}]

这是包含该 json 列的表：

CREATE TABLE USER_CONFIGURATIONS ( ID BIGSERIAL PRIMARY KEY, DATA JSONB ); 

添加新字段很容易我正在使用：

UPDATE USER_CONFIGURATIONS
SET DATA = DATA || '{"name":"firstconxsf", "theme":"dassrk", "textsize":"lassrge"}'
WHERE id = 9;

但是如何使用 id = 1 或 2 更新单

标签： arrayspostgresqlsql-updatejsonb

Answer 1

点击：逐步演示：db<>fiddle

UPDATE users                                                   -- 4
SET data = s.updated
FROM (
    SELECT
        jsonb_agg(                                             -- 3
            CASE                                               -- 2
                WHEN ((elem ->> 'id')::int IN (1,2)) THEN
                    elem || '{"name":"abc", "icon":"HUGE"}'
                ELSE elem
            END
        ) AS updated
    FROM
        users,
        jsonb_array_elements(data) elem                        -- 1
) s;

1. 将数组元素分别展开为一行
2. 如果元素有相关的id，用||操作符更新；如果没有，请保留原件
3. 更新 JSON 数据后重新聚合数组
4. 执行UPDATE语句。