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 test (data jsonb);
CREATE TABLE
insert into test (data)
select '[{
"x_id": "1",
"type": "t1",
"parts": [
{ "part_id": "1", "price": 400 },
{ "part_id": "2", "price": 500 },
{ "part_id": "3", "price": 0 }
]
},
{
"x_id": "2",
"type": "t1",
"parts": [
{ "part_id": "1", "price": 1000 },
{ "part_id": "3", "price": 60 }
]
},
{
"x_id": "3",
"type": "t2",
"parts": [
{ "part_id": "1", "price": 100 },
{ "part_id": "3", "price": 780 },
{ "part_id": "2", "price": 990 }
]
}
]';
INSERT 0 1
update test as t2 set
data = t.data #- p.path
from test as t
cross join lateral (
select array[(a.i-1)::text,'parts',(b.i-1)::text]
from jsonb_array_elements(t.data) with ordinality as a(data,i),
jsonb_array_elements(a.data->'parts') with ordinality as b(data,i)
where
a.data ->> 'x_id' = '2' and
b.data ->> 'part_id' = '23232'
) as p(path)
where
t.ctid = t2.ctid
UPDATE 0
select jsonb_pretty(data) from test
| jsonb_pretty |
|---|
| [ { "type": "t1", "x_id": "1", "parts": [ { "price": 400, "part_id": "1" }, { "price": 500, "part_id": "2" }, { "price": 0, "part_id": "3" } ] }, { "type": "t1", "x_id": "2", "parts": [ { "price": 1000, "part_id": "1" }, { "price": 60, "part_id": "3" } ] }, { "type": "t2", "x_id": "3", "parts": [ { "price": 100, "part_id": "1" }, { "price": 780, "part_id": "3" }, { "price": 990, "part_id": "2" } ] } ] |
SELECT 1