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 groups ( name VARCHAR, grp JSONB);
INSERT INTO groups (name, grp) VALUES
('joe', '[{"gid": "1", "ona": "joe", "tit": "group 1 "}, {"gid": "2", "ona": "harry", "tit": "tester1 group 2"}, {"gid": "3", "ona": "moe", "tit": "group 3"}]'),
('harry', '[{"gid": "1", "ona": "joe", "tit": "group 1 "}, {"gid": "2", "ona": "harry", "tit": "tester1 group 2"}, {"gid": "3", "ona": "moe", "tit": "group 3"}]'),
('moe' , '[{"gid": "1", "ona": "joel", "tit": "group 1 "}, {"gid": "2", "ona": "harry", "tit": "tester1 group 2"}, {"gid": "3", "ona": "moe", "tit": "group 3"}]')
INSERT 0 3
WITH T AS
(
SELECT ('{'||index-1||',tit}')::text[] AS path
FROM groups AS g2
CROSS JOIN jsonb_array_elements(grp)
WITH ORDINALITY arr(j,index)
WHERE j->>'gid'='1'
)
UPDATE groups AS g
SET grp = jsonb_set(grp,t.path,'"newTitle Group 1 "',false)
FROM t
UPDATE 3
SELECT g.*
FROM groups AS g
| name | grp |
|---|---|
| joe | [{"gid": "1", "ona": "joe", "tit": "newTitle Group 1 "}, {"gid": "2", "ona": "harry", "tit": "tester1 group 2"}, {"gid": "3", "ona": "moe", "tit": "group 3"}] |
| harry | [{"gid": "1", "ona": "joe", "tit": "newTitle Group 1 "}, {"gid": "2", "ona": "harry", "tit": "tester1 group 2"}, {"gid": "3", "ona": "moe", "tit": "group 3"}] |
| moe | [{"gid": "1", "ona": "joel", "tit": "newTitle Group 1 "}, {"gid": "2", "ona": "harry", "tit": "tester1 group 2"}, {"gid": "3", "ona": "moe", "tit": "group 3"}] |
SELECT 3