英文:
How to add new key-value pair in each object of JSONB Array- PostgreSQL
问题
以下是翻译好的部分:
我在我的PostgreSQL数据库中有一张名为master_data_approval_table的表,其中有一个字段approval_value(类型为Jsonb),其中包含以下JSON结构:
[{
"name": "abc",
"email": "abc.pqr@gmail.com"
},
{
"name": "xyz",
"email": "xyz.pqr@gmail.com"
}]
现在我们想要在数组中的每个对象中添加一个键值对,即**"comment": null**,就像下面的示例一样:
[{
"name": "abc",
"email": "abc.pqr@gmail.com",
"comment": null
},
{
"name": "xyz",
"email": "xyz.pqr@gmail.com",
"comment": null
}]
此外,我们需要对表中的所有记录执行此操作。我已尝试不同的现有答案和查询,但没有帮助,因此请有人指导我如何在PostgreSQL中为上述情况编写查询。
提前致谢。
英文:
I have a table master_data_approval_table in my postgres DB having field approval_value (type Jsonb) which contains below JSON structure:
[{
"name": "abc",
"email": "abc.pqr@gmail.com"
},
{
"name": "xyz",
"email": "xyz.pqr@gmail.com"
}
]
Now we want to add one more key-value pair i.e. "comment": null in each object of the array like the below one:
[{
"name": "abc",
"email": "abc.pqr@gmail.com",
"comment": null
},
{
"name": "xyz",
"email": "xyz.pqr@gmail.com",
"comment": null
}
]
In more to that we need to do this for all the records of the table.
I have tried different existing answers and queries but no help so please somebody guide me on how to write a query in PostgreSQL for the above scenario.
Thanks in advance.
答案1
得分: 1
你可以使用 jsonb_array_elements()
来展开 JSON 数组,使用 ||
来修改每个对象以添加新的键/值对,最后使用 jsonb_agg()
来聚合回原始的顺序:
select t.approval_value, e.new_approval_value
from master_data_approval_table t
cross join lateral (
select jsonb_agg( e.elt || '{"comment": null}' order by e.seq) new_approval_value
from jsonb_array_elements(t.approval_value) with ordinality as e(elt, seq)
) e
如果你想实际修改列中的值,你可以使用 update
:
update master_data_approval_table t
set approval_value = (
select jsonb_agg( e.elt || '{"comment": null}' order by e.seq) new_approval_value
from jsonb_array_elements(t.approval_value) with ordinality as e(elt, seq)
)
英文:
You can unnest the JSON array with jsonb_array_elements()
, use ||
to modify each object to add the new key/value, and finally aggregate back with jsonb_agg()
, with respect to the original ordering:
select t.approval_value, e.new_approval_value
from master_data_approval_table t
cross join lateral (
select jsonb_agg( e.elt || '{"comment": null}' order by e.seq) new_approval_value
from jsonb_array_elements(t.approval_value) with ordinality as e(elt, seq)
) e
approval_value | new_approval_value |
---|---|
[<br> {<br> "name": "abc",<br> "email": "abc.pqr@gmail.com"<br> },<br> {<br> "name": "xyz",<br> "email": "xyz.pqr@gmail.com"<br> }<br>] | [<br> {<br> "name": "abc",<br> "email": "abc.pqr@gmail.com",<br> "comment": null<br> },<br> {<br> "name": "xyz",<br> "email": "xyz.pqr@gmail.com",<br> "comment": null<br> }<br>] |
If you wanted to actually modify the values in the column, you would update
:
update master_data_approval_table t
set approval_value = (
select jsonb_agg( e.elt || '{"comment": null}' order by e.seq) new_approval_value
from jsonb_array_elements(t.approval_value) with ordinality as e(elt, seq)
)
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论