By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
drop table if exists my_table;
create table my_table as
select
'{
"applicants": [
{
"last_name": "ss",
"first_name": "ss",
"age": 31
},
{
"last_name": "kk",
"first_name": "kk",
"age": 32
}
]
}'::jsonb as json_col;
DROP TABLE
SELECT 1
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)
)
returning jsonb_pretty(json_col)
| jsonb_pretty |
|---|
| { "applicants": [ { "data": { "age": 31, "first_name": "ss" }, "last_name": "ss" }, { "data": { "age": 32, "first_name": "kk" }, "last_name": "kk" } ] } |
UPDATE 1