Postgresql

如何更新復雜的 jsonb 列?

  • June 26, 2019

我有一個具有以下定義的表:

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_idtask_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}]}';

我建議使用***LATERALjoin***,以排除在普通連接中可能錯誤地將多個匹配行集中在一起的可能性。

至**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索引:

引用自:https://dba.stackexchange.com/questions/146683