在SQL函数中,我有一个jsonb数组的格式如下:
[{id: 1, name: John}, {id: 2, name: Jane}]
我想把这个 struct 转换成一个jsonb,使用id作为键:
{
1: {id: 1, name: John},
2: {id: 2, name: Jane}
}
在SQL函数中,我有一个jsonb数组的格式如下:
[{id: 1, name: John}, {id: 2, name: Jane}]
我想把这个 struct 转换成一个jsonb,使用id作为键:
{
1: {id: 1, name: John},
2: {id: 2, name: Jane}
}
您可以使用jsonb_array_elements()
分解数组,然后重新聚合为一个具有jsonb_object_agg()
的对象.Demo at db<>fiddle:
select jsonb_object_agg(_element->>'id', _element)
from my_table
cross join lateral jsonb_array_elements(my_table.jdata) as _an(_element)
group by my_table.ctid
jsonb_object_agg |
---|
{"1": {"id": 1, "name": "John"}, "2": {"id": 2, "name": "Jane"}} |
{"1": {"id": 1, "name": "John"}, "2": {"id": 2, "name": "Ted"}, "11": {"id": 11, "name": "Jane"}, "111": {"id": 111, "name": "Bob"}} |
你不能使用JSON number作为密钥,这就是为什么我使用->>
访问器转换为text
.不知道你的主键,我按system column ctid
分组,因为它总是存在和唯一的,但你可能想要你的实际PK.cross join lateral
可以用单个逗号,
替换,但显式join
语法通常优于旧的隐式风格.
如果你不希望实际的id
,而是希望数组中的索引作为新键,你可以使用with ordinality
来获得这些:
select jsonb_object_agg(n::text, e)
from my_table
, jsonb_array_elements(jdata) with ordinality as elements(e,n)
group by my_table.ctid
update
,而不是select
:
update my_table set jdata=
(select jsonb_object_agg(e->>'id', e)
from jsonb_array_elements(jdata) e )
returning jsonb_pretty(jdata);
jsonb_pretty |
---|
{ "1": { "id": 1, "name": "John" }, "2": { "id": 2, "name": "Jane" } } |
{ "1": { "id": 1, "name": "John" }, "2": { "id": 2, "name": "Ted" }, "11": { "id": 11, "name": "Jane" }, "111": { "id": 111, "name": "Bob" } } |
请注意,jsonb
array始终保持其原始顺序为重要,但结果的jsonb
object将由PostgreSQL根据键自由重新排序.