更新嵌套在 JSON 列 (Postgres) 中的 JSON 數組的所有元素

我有 json 列，其中的數據如下所示：

CREATE TABLE foo(mycol)
AS SELECT $${
   "a": [
       { "q": 5 },
       { "q": 8 },
       { "q": 10 }
   ]
}$$::jsonb;

我想映射數組中的元素並添加一個額外的屬性來獲得這樣的東西：

{
   a: [
       { q: 5, r: [] },
       { q: 8, r: [] },
       { q: 10, r: [] }
   ]
}

任何和所有將不勝感激。

PostgreSQL 9.6.6

我想我解決了。就像是：

SELECT my_column::jsonb ||
   jsonb_build_object('a',
           (SELECT json_agg(a::jsonb || jsonb_build_object('r','[]'::jsonb))
           FROM jsonb_array_elements(my_column->'a') a)
   )
FROM my_table;

雖然這可能並不完美，因為我在沒有測試的情況下翻譯了這個例子。

而不是建構一個新對象，您可以使用jsonb_set

SELECT jsonb_set(
 mycol,
 ARRAY['a'],
 jsonb_agg(x || '{"r":[]}'::jsonb),
 true
)
FROM foo
CROSS JOIN LATERAL jsonb_array_elements(mycol->'a')
 AS t(x)
GROUP BY mycol;

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