在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根据键自由重新排序.