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?.
create table mytable (id int, items jsonb);
insert into mytable values (1, '
{
"items": [
{
"itemQty": 2,
"itemName": "snake"
},
{
"itemQty": 1,
"itemName": "x kodiyum"
}
]
}');
1 rows affected
select * from mytable;
| id | items |
|---|---|
| 1 | {"items": [{"itemQty": 2, "itemName": "snake"}, {"itemQty": 1, "itemName": "x kodiyum"}]} |
select jsonb_build_object(
'items', jsonb_agg(
jsonb_build_object(
'itemQty', (x.obj ->> 'itemQty')::text,
'itemName', x.obj ->> 'Name'
)
)
)new_items
from mytable t
cross join lateral jsonb_array_elements(t.items -> 'items') as x(obj)
group by id
| new_items |
|---|
| {"items": [{"itemQty": "2", "itemName": null}, {"itemQty": "1", "itemName": null}]} |
update mytable t
set items = (
select jsonb_build_object(
'items', jsonb_agg(
jsonb_build_object(
'itemQty', (x.obj ->> 'itemQty')::text,
'itemName', x.obj ->> 'Name'
)
)
)
from jsonb_array_elements(t.items -> 'items') as x(obj)
)
1 rows affected
select * from mytable;
| id | items |
|---|---|
| 1 | {"items": [{"itemQty": "2", "itemName": null}, {"itemQty": "1", "itemName": null}]} |