Postgresql:在json数组对象中追加元素

Question

我有以下jsonb数据存储在表列中，如下所示：

'{"info":[
{
"database": "Oracle",
"company" : "Oracle"
},
{
"database":"Sql Server",
"company" :"Microsoft"
},
{
"database":"DB2",
"company" :"IBM"
}
]}'

我需要附加一个元素"strong"；许可证"："专有的"到json数组中的所有对象，如下所示：

'{"info":[
{
"database": "Oracle",
"company" : "Oracle",
"License" : "Proprietary"
},
{
"database":"Sql Server",
"company" :"Microsoft",
"License" : "Proprietary"
},
{
"database":"DB2",
"company" :"IBM",
"License" : "Proprietary"
}
]}'

我在这里添加了sqlfiddle链接：https://dbfiddle.uk/?rdbms=postgres_11&fiddle=3990e79140df1c897aac2fb19364d2e9

提前谢谢。

Answer 1

您需要分别对每个数组元素调用set_jsonb，用jsonb_array_elements获取值，并用jsonb_agg:将它们聚合回json数组

UPDATE test
SET data = jsonb_set(data, '{info}', (
SELECT jsonb_agg(el || '{"License" : "Proprietary"}')
FROM jsonb_array_elements(data -> 'info') el
));

^{(更新的dbfiddle演示(}