更新jsonbPostgreSQL中陣列的物件欄位

我在 PostgreSQL 中有下表：

CREATE TABLE resume (
  resume_id UUID PRIMARY KEY,
  data JSONB
);

在該表中，我有一列data具有JSONB資料型別并包含如下值：

{"educations": [{"major": "MAJOR-1", "minor": "MINOR-1"}, {"major": "MAJOR-2", "minor": "MINOR-2"}]}

這是測驗資料：

INSERT INTO resume VALUES('7e29d793-a4ba-4bfb-a93a-c2d34b7a5c8a', '{"educations": [{"major": "MAJOR-1", "minor": "MINOR-1"}, {"major": "MAJOR-2", "minor": "MINOR-2"}]}');
INSERT INTO resume VALUES('7e29d793-a4ba-4bfb-a93a-c2d34b7a5c8b', '{"educations": [{"major": "ANOTHER-MAJOR-1", "minor": "ANOTHER-MINOR-1"}, {"major": "ANOTHER-MAJOR-2", "minor": "ANOTHER-MINOR-2"}]}');

但是現在我需要將主要和次要值轉換為陣列，因此，對于第一行我想收到這個結果：

{"educations": [{"major": ["MAJOR-1"], "minor": ["MINOR-1"]}, {"major": ["MAJOR-2"], "minor": ["MINOR-2"]}]}

對于第二行，我想收到這個結果：

 {"educations": [{"major": ["ANOTHER-MAJOR-1"], "minor": ["ANOTHER-MINOR-1"]}, {"major": ["ANOTHER-MAJOR-2"], "minor": ["ANOTHER-MINOR-2"]}]}

現在我已經創建了這個查詢來更新major：

with sub as (
    select pos - 1 as elem_index, elem, resume_id
    from resume, jsonb_array_elements(data -> 'educations') with ordinality arr(elem, pos)
)
update resume cv
set data = jsonb_set(data, array['educations', sub.elem_index::text, 'major'], ('[' || (sub.elem -> 'major')::text || ']')::jsonb, true)
from sub
where cv.resume_id = sub.resume_id

但它只更新了所有行的陣列的第一個元素，所以現在我收到這個結果：

{"educations": [{"major": ["MAJOR-1"], "minor": "MINOR-1"}, {"major": "MAJOR-2", "minor": "MINOR-2"}]}
{"educations": [{"major": ["ANOTHER-MAJOR-1"], "minor": "ANOTHER-MINOR-1"}, {"major": "ANOTHER-MAJOR-2", "minor": "ANOTHER-MINOR-2"}]}

所以我的問題是如何解決這個問題？請幫我 ：）

uj5u.com熱心網友回復：

解決方案1：基于jsonb更新 jsonb_set

jsonb_set()無法對相同的 jsonb 資料進行多次更新，因此您需要創建一個aggregate基于jsonb_set()并將迭代一組行的函式：

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

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

jsonb_set_agg()然后您可以在以下查詢中使用聚合函式：

SELECT resume_id
     , jsonb_set_agg(r.data, array['educations', (a.id - 1) :: text, b.key], to_jsonb(array[b.value]), True)
  FROM resume AS r
 CROSS JOIN LATERAL jsonb_array_elements(r.data->'educations') WITH ORDINALITY AS a(data, id)
 CROSS JOIN LATERAL jsonb_each_text(a.data) AS b
 GROUP BY resume_id

最后在更新宣告中：

WITH sub AS (
    SELECT resume_id
         , jsonb_set_agg(r.data, array['educations', (a.id - 1) :: text, b.key], to_jsonb(array[b.value]), True) AS data
      FROM resume AS r
     CROSS JOIN LATERAL jsonb_array_elements(r.data->'educations') WITH ORDINALITY AS a(data, id)
     CROSS JOIN LATERAL jsonb_each_text(a.data) AS b
     GROUP BY resume_id
)
UPDATE resume cv
   SET data = sub.data
  FROM sub
 WHERE cv.resume_id = sub.resume_id

解決方案 2：分解并重建 jsonb 資料

SELECT jsonb_agg(c.data ORDER BY c.id)
  FROM
     ( SELECT resume_id
            , a.id
            , jsonb_object_agg(b.key, array[b.value]) AS data
         FROM resume AS r
        CROSS JOIN LATERAL jsonb_array_elements(r.data->'educations') WITH ORDINALITY AS a(data, id)
        CROSS JOIN LATERAL jsonb_each_text(a.data) AS b
        GROUP BY resume_id, a.id
     ) AS c
 GROUP BY c.resume_id

在dbfiddle中查看測驗結果。

標籤：

上一篇：如何計算TeradataSQL中每個客戶端的平均數量？

下一篇：PostgreSQL：在SQL陳述句中使用常量