Postgresql
如何更新復雜的 jsonb 列?
我有一個具有以下定義的表:
create table json_test ( filter_data jsonb);
我向它插入這樣的值:
'{"task_packets": [ { "state": "PROCEEDING", "task_id": 1001 }, { "state": "REVERTING", "task_id": 1002 } ]}'
現在我想將此
jsonb
列更新為:'{"task_packets": [ { "state": "DONE", "task_id": 1001 }, { "state": "REVERTING", "task_id": 1002 } ]}'
即我想用
task_id
task_packets 數組中指定的值更改狀態。我建議以某種方式將jsonb_set()
函式與#-
運算符結合使用(首先從數組中刪除值,然後以更新的狀態附加到它)。我該怎麼做?
就像我評論的那樣,使用標準化的數據庫佈局會更有效,使用這樣的表
CREATE TABLE task_packets ( task_id int PRIMARY KEY , state text NOT NULL -- or: state_id int NOT NULL REFERENCES state(state_id) ... );
除此之外,我們可以有一個 PK 約束來強制執行唯一的
task_id
數字。而UPDATE
你想要的是微不足道的。但要回答提出的問題:
至**
SELECT
**:SELECT * FROM json_test jt , LATERAL ( SELECT jsonb_set(filter_data , '{task_packets}' , jsonb_agg(CASE WHEN elem->>'task_id' = '1001' THEN jsonb_set(elem, '{state}', to_jsonb(text 'DONE')) ELSE elem END)) AS filter_data_new FROM jsonb_array_elements(filter_data->'task_packets') elem ) tp WHERE jt.filter_data @> '{"task_packets": [{"task_id": 1001}]}';
我建議使用***
LATERAL
join***,以排除在普通連接中可能錯誤地將多個匹配行集中在一起的可能性。至**
UPDATE
**:UPDATE json_test SET filter_data = ( SELECT jsonb_set(filter_data , '{task_packets}' , jsonb_agg(CASE WHEN elem->>'task_id' = '1001' THEN jsonb_set(elem, '{state}', to_jsonb(text 'DONE')) ELSE elem END)) FROM jsonb_array_elements(filter_data->'task_packets') elem ) WHERE filter_data @> '{"task_packets": [{"task_id": 1001}]}';
相同的可以用在(或在)中的相關子查詢來實現。
UPDATE``SELECT
為了使大表的速度更快,請確保有一個適當的索引,最好是
jsonb_path_ops
索引: