將鍵和值添加到鍵尚不存在的 jsonb 數組的元素中

我有一個tbl具有以下結構的表：

CREATE TABLE tbl (
 org  text
, data jsonb
);

欄位中的數據jsonb是一個數組，結構如下：

INSERT INTO tbl VALUES
('SOMETHING'
, '[
     {
       "type": "XYZ",
       "valueA": "500",
       "valueB": "ABC"
     },
     {
       "type": "ABC",
       "valueA": "300",
       "valueB": "CDE"
     }
  ]')
;

我想將一個鍵添加到對象具有值為 的鍵valueC的元素中。data``"type"``"XYZ"

valueC的值將是一個字元串數組。數組的值將取決於org列的值。

我想對所有行執行此操作，如果存在特定行org，並且列中的jsonb數組data包含帶有 的對象"type": "XYZ"，那麼我會得到以下結果：

[
 {
   "type": "XYZ",
   "valueA": "500",
   "valueB": "ABC",
   "valueC": ["SOMETHING"],
 },
 {
   "type": "ABC",
   "valueA": "300",
   "valueB": "CDE",
 }
]

我還想確保此腳本僅valueC在與條件匹配的對像中不存在時才執行，因此除非需要，否則在遷移/回滾期間不會重新執行。

這是我到目前為止所擁有的，但是當它沒有找到子查詢的結果並且我無法弄清楚如何僅在valueC不存在的情況下執行它時它不起作用：

UPDATE tbl SET  
 data = jsonb_set(
   data, 
   '{data}', 
  (SELECT jsonb_agg(elem ||'{"valueC":["SOMETHING"]}') FROM jsonb_array_elements(data->'data') as elem where elem ->> 'type' = 'XYZ')
 )
WHERE org = 'SOMETHING';

SQL/JSON 路徑表達式使這變得簡短而高效。需要Postgres 12 或更高版本：

UPDATE tbl
SET    data = (SELECT jsonb_agg(CASE WHEN jsonb_path_exists(elem, '$ ? (@.type == "XYZ") ? (!exists (@.valueC))')
                                    THEN elem || '{"valueC":["SOMETHING"]}'
                                    ELSE elem END)
              FROM   jsonb_array_elements(data) AS elem)
WHERE  org = 'SOMETHING'
AND    jsonb_path_exists(data, '$[*] ? (@.type == "XYZ") ? (!exists (@.valueC))')
RETURNING *;

db<>在這裡擺弄

這允許一次更新多個符合條件的數組元素。

WHERE首先，在子句中廉價地辨識符合條件的行。（我們不想處理所有行。）

然後，在取消嵌套之後jsonb_array_elements()，重新檢查CASE表達式以僅修改符合條件的數組元素。

如果表很大，一定要有索引。看：

• 在 JSONB 記錄數組中查找包含鍵的行

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