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
UPDATE groups AS g
SET grp = REPLACE(grp::text, '"tit": "group 1 "','"tit": "newTitle Group 1 "')::json
WHERE g.name IN ( SELECT g.name
FROM groups AS g
CROSS JOIN jsonb_array_elements(grp) AS j
WHERE j.value->>'ona' = 'joe' )
UPDATE 2
SELECT *
FROM groups
| name | grp |
|---|---|
| moe | [{"gid": "1", "ona": "joel", "tit": "group 1 "}, {"gid": "2", "ona": "harry", "tit": "tester1 group 2"}, {"gid": "3", "ona": "moe", "tit": "group 3"}] |
| 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"}] |
SELECT 3