postgresql - Postgres：更新 JSON 数组并删除与键匹配的元素

Question

鉴于下表

drop table if exists documents;
create table documents(docu_id text, attachments jsonb);
insert into documents values
('001', 
'[{"province":"test province","city":"test city","barangay":"test barangay,"street":"test ST"},
 {"province":"test province 2","city":"test city 2","barangay":"test barangay 2","street":"test street 2"}]'
),

('002', 
'[{"province":"test province 2 1":"VALENZUELA CITY","test barangay 2 1":"test barangay 2 1","street":"test street 2 1"},
 {"province":"test province 2 2","city":"test city 2 2","barangay":"test barangay 2 2","street":"test strett 2 2"}]'
);

我如何更新匹配街道关键词的 json 数组：test ST

预期产出

[{"province":"test province 2","city":"test city 2","barangay":"test barangay 2","street":"test street 2"}]

[{"province":"test province 2 1":"VALENZUELA CITY","test barangay 2 1":"test barangay 2 1","street":"test street 2 1"},
     {"province":"test province 2 2","city":"test city 2 2","barangay":"test barangay 2 2","street":"test strett 2 2"}]

标签： postgresqlpostgresql-9.4

Answer 1

演示：db<>小提琴

SELECT
    docu_id,
    jsonb_agg(elements.value)                     -- 3
FROM
    documents,
    jsonb_array_elements(attachments) AS elements -- 1
WHERE elements ->> 'street' != 'test ST'          -- 2
GROUP BY docu_id                                  -- 3

1. 将数组元素展开为一行jsonb_array_elements()
2. 按您喜欢的方式过滤这些元素
3. 使用聚合函数将剩余元素再次分组到 JSON 数组中jsonb_agg()

---

更新：

UPDATE documents d
SET attachments = s.new_attachments
FROM (
    SELECT
        docu_id,
        jsonb_agg(elements.value) AS new_attachments
    FROM
        documents,
        jsonb_array_elements(attachments) AS elements
    WHERE elements.value ->> 'street' != 'test ST'
    GROUP BY docu_id
) s
WHERE d.docu_id = s.docu_id

演示：db<>小提琴