更新嵌套在 JSON 對像數組中的給定鍵的所有值

我在 Postgres 表中有一個jsonb列。它包含以下數據：data``my_table

[
  {"id":"1","status":"test status1","updatedAt":"1571145003"},
  {"id":"2","status":"test status2","updatedAt":"1571145323"}
]

我想updatedAt使用一個查詢更新該數組中所有對象的鍵。我試過：

update my_table set data = data || '{"updatedAt": "1571150000"}';

上面的查詢在數組中添加了一個新對象，如下所示：

[
  {"id":"1","status":"test status1","updatedAt":"1571145003"},
  {"id":"2","status":"test status2","updatedAt":"1571145323"},
  {"updatedAt":"1571150000"}
]

我想要這樣的輸出：

[
  {"id":"1","status":"test status1","updatedAt":"1571150000"},
  {"id":"2","status":"test status2","updatedAt":"1571150000"}
]

我也試過jsonb_set()了，但這需要第二個參數是數組索引。我無法確定數組中 JSON 對象的數量。

如果這可以通過自定義函式解決，也可以。

第一次取消嵌套數組的所有元素，第二次更新每個元素，然後簡單地更新原始表再次建構數組。

with ct as
(
   select id, jsonb_array_elements(data) dt
   from   t
)
, ct2 as
(
 select id, jsonb_set(dt, '{updatedAt}', '"1571150000"', false) dt2
 from   ct
)
update t
set    data = (select jsonb_agg(dt2) from ct2 where ct2.id = t.id);

select * from t;

編號 | 數據 
-: | :-----------------------------------------------------------------------------------------------------------------------------------
 1 | [{“id”：“1”，“status”：“test status1”，“updatedAt”：“1571150000”}，{“id”：“2”，“status”：“test status2”，“updatedAt”： "1571150000"}]

db<>在這裡擺弄

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