動態更新 JSONB 行 - POSTGRESQL

我目前正在嘗試更新我們數據庫中的一些 JSONB 欄位，我們正在將 JSON 中的節點移動到特定欄位的上一級。

然而，我們試圖移動的節點不是固定的；有時它的索引可以是 1、2 或 3。

我目前有這個查詢：

UPDATE table1 t1 SET jsonbfield = jsonb_set(jsonfield ,
           '{field1,0,field2,0,configuration}',
           (select jsonbfield->'field1'->0->'field2'->0->'field3'->0->'configuration'
           from table1 where
           jsonbfield->'field1'->0->'field2'->0->'field3'->0->>'name'='SOME_NAME'
           and id=t1.id), true)
           where jsonbfield->'field'->0->'field2'->0->'field3'->0->>'name'='SOME_NAME';

如果我們嘗試移動的節點始終位於位置 0，這基本上可以滿足要求，但是，如前所述，我們注意到我們要向上移動的節點可以位於另一個位置。

有沒有辦法遍歷 JSON 中的每一行，獲取我們要移動的節點的位置，將其保存到變數中並將其放在更新語句中？

我嘗試過使用 do for ，但似乎它與 select 的互動效果不佳jsonb_array_elements。

樣本數據

{
  "parent":[
     {
        "steps":[
           {
              "name":"CONFIG1",
              "index":1,
              "childStep":[
                 {
                    "configuration":{
                       
                    }
                 }
              ]
           },
           {
              "name":"CONFIG2",
              "index":2,
              "childStep":[
                 {
                    "configuration":{
                        "configInfo":{
                          "info":"someinfo"
                       }
                    }
                 }
              ]
           }
        ]
     }
  ]
}

此 json 表示jsonbfieldtable1 中的行。我們要做的是將配置節點 CONFIG2 從 childStep 移動到 Step Level，所以更新後它看起來像這樣：

{
  "parent":[
     {
        "steps":[
           {
              "name":"CONFIG1",
              "index":1,
              "childStep":[
                 {
                    "configuration":{
                       
                    }
                 }
              ]
           },
           {
              "name":"CONFIG2",
              "index":2,
              "configuration":{
                        "configInfo":{
                          "info":"someinfo"
                       }
                    }
           }
        ]
     }
  ]
}

我發布的查詢已經將欄位向上移動，但是，問題在於我們不知道 CONFIG2 是否將位於索引 0、1、2 等處。所以我們試圖找到一種方法來動態獲取位置它的位置，然後更新它。對於此處發布的目前查詢，它假定 CONFIG2 始終位於索引 1（或 0，第一個位置）。

UPDATE table1
SET    jsonfield = (
  SELECT jsonb_set(jsonfield
                 , '{parent,0,steps}'::text[] || (idx - 1)::text
                 ,  val->'childStep'->0 || (val - text 'childStep')
                  )
  FROM   jsonb_array_elements(jsonfield#>'{parent,0,steps}') WITH ORDINALITY a(val, idx)
  WHERE  val->>'name' = 'CONFIG2'
  )
WHERE  jsonfield @> '{"parent":[{"steps":[{"name":"CONFIG2"}]}]}';
-- WHERE  jsonfield @? '$.parent[0].steps[*].name ? (@ == "CONFIG2")';  -- equivalent alternative

db<>在這裡擺弄

jsonb我在相關子查詢中計算新值。

WITH ORDINALITY使用 .取消嵌套 JSON 數組時生成數組索引jsonb_array_elements()。後者idx - 1從 Postgres 數組從 1 開始和 JSON 數組從 0 開始調整 off-by-1 錯誤。更多關於WITH ORDINALITY：

• 如何保留未嵌套數組中元素的原始順序？

我添加了一個外部WHERE條件，以便只處理適用的行。避免更新值不變的行，因為這會增加成本而不會帶來好處。

您可以在 Postgres 12 或更高版本的SQL/JSON 路徑語言@>中使用 contains 運算符或 new 運算符。兩者都可以使用索引。看：@?

• 更新嵌套在 JSON 對像數組中的給定鍵的所有值
• 按jsonb中數組內子鍵內的子鍵索引

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