將新的鍵/值對添加到PostgreSQLJSON列內的嵌套陣列中

使用 PostgreSQL 13.4，我有一個包含 JSON 列的表，其結構類似于以下示例：

{
  "username": "jsmith",
  "location": "United States",
  "posts": [
    {
      "id":"1",
      "title":"Welcome",
      "newKey":true <----------- insert new key/value pair here
    },
    {
      "id":"4",
      "title":"What started it all",
      "newKey":true <----------- insert new key/value pair here
    }
  ]
}

為了更改第一級的鍵，我使用了一個像這樣的簡單查詢

UPDATE
    sample_table_json
SET
    json = json::jsonb || '{"active": true}';

但這不適用于示例中的嵌套物件和陣列中的物件。如何將鍵/值對插入到陣列中包含嵌套物件的 JSON 列中？

uj5u.com熱心網友回復：

您必須jsonb_set在指定正確路徑的同時使用該功能，請參閱手冊。

對于單個 json 更新：

UPDATE sample_table_json
  SET json = jsonb_set( json::jsonb
                      , '{post,0,active}'
                      , 'true'
                      , true
                      )

對于（非常）有限的一組 json 更新：

UPDATE sample_table_json
   SET json = jsonb_set(jsonb_set( json::jsonb
                                 , '{post,0,active}'
                                 , 'true'
                                 , true
                                 )
                       , '{post,1,active}'
                       , 'true'
                       , true
                       )

對于相同 json 資料的更大一組 json 更新，您可以創建jsonb_set函式的“聚合版本” ：

CREATE OR REPLACE FUNCTION jsonb_set(x jsonb, y jsonb, p text[], e jsonb, b boolean)
RETURNS jsonb LANGUAGE sql AS $$
SELECT jsonb_set(COALESCE(x,y), p, e, b) ; $$ ;

CREATE OR REPLACE AGGREGATE jsonb_set_agg(x jsonb, p text[], e jsonb, b boolean)
( STYPE = jsonb, SFUNC = jsonb_set) ;

然后jsonb_set_agg在迭代查詢結果時使用新的聚合函式，其中可以計算路徑和 val 欄位：

SELECT jsonb_set_agg('{"username": "jsmith","location": "United States","posts": [{"id":"1","title":"Welcome"},{"id":"4","title":"What started it all"}]}' :: jsonb
                    , l.path :: text[]
                    , to_jsonb(l.val)
                    , true)
 FROM (VALUES ('{posts,0,active}', 'true'), ('{posts,1,active}', 'true')) AS l(path, val) -- this list could be the result of a subquery

這個查詢最終可以用來更新一些資料：

WITH list AS
(
SELECT id
     , jsonb_set_agg(json :: jsonb
                    , l.path :: text[]
                    , to_jsonb(l.val)
                    , true) AS res
 FROM sample_table_json
CROSS JOIN (VALUES ('{posts,0,active}', 'true'), ('{posts,1,active}', 'true')) AS l(path, val) 
GROUP BY id
)
UPDATE sample_table_json AS t
   SET json = l.res
  FROM list AS l
 WHERE t.id = l.id

在dbfiddle 中查看測驗結果

uj5u.com熱心網友回復：

它變得有點復雜。回圈遍歷陣列，將新的鍵/值對添加到每個陣列元素并重新聚合陣列，然后重建整個物件。

with t(j) as 
(
 values ('{
  "username": "jsmith",
  "location": "United States",
  "posts": [
    {
      "id":"1", "title":"Welcome", "newKey":true
    },
    {
      "id":"4", "title":"What started it all", "newKey":true
    }]
 }'::jsonb)
)
select j || 
 jsonb_build_object
 (
  'posts', 
  (select jsonb_agg(je||'{"active":true}') from jsonb_array_elements(j->'posts') je)
 )
from t;

標籤：json PostgreSQL的

上一篇：產品，添加到購物車的cookie值未顯示在ListView中

下一篇：NodeJsjavascript：在分配下一個屬性之前等待分配前一個物件屬性