PSQL -查询JSONB元素的转置

Question

我有一个jsonb对象，像这样:

{
"applicants": [
{
"last_name": "ss",
"first_name": "ss",
"age": 31
},
{
"last_name": "kk",
"first_name": "kk",
"age": 32
}
]
}

我想把它转换成。

{
"applicants": [
{
"last_name": "ss",
"data": {            
"first_name": "ss",
"age": 31
}
},
{
"last_name": "kk",
"data": {            
"first_name": "kk",
"age": 32
}
}
]
}

我以前使用jsonb_array_elements和jsonb_build_object做过类似的事情，但我不知道如何在每个对象中创建一个新的数据对象，并将字段转置到其中。

是否可以在普通的psql查询中写这个?

谢谢。

Answer 1

我必须指出Postgres不是修改JSON数据结构的最佳工具，如果您觉得需要这样做，这可能意味着您的解决方案通常不是最佳的。虽然Postgres有必要的功能来做到这一点，但我不想维护包含如下查询的代码:

update my_table set
json_col = (
select 
jsonb_build_object(
'applicants',
jsonb_agg(
elem- 'first_name'- 'age' || jsonb_build_object(
'data', 
jsonb_build_object(
'first_name', 
elem->'first_name', 
'age', 
elem->'age'
)
)
)
)
from jsonb_array_elements(json_col->'applicants') as arr(elem)
)

在db<>fiddle中测试。