如何更新復雜的 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_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索引：

• 在 Postgres jsonb 中查詢數組結構的正確索引是什麼？

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