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 view (id int, content jsonb);
insert into view values (0,'[{"owner":"foo", "value": 1},{"owner":"bar", "value": 2},{"owner":"foo", "value": 3}]'::jsonb);
insert into view values (1,'[{"owner":"bar", "value": 3},{"owner":"bar", "value": 5},{"owner":"foo", "value": 6}]'::jsonb);
insert into view values (2,'[{"owner":"foo", "value": 4},{"owner":"foo", "value": 8},{"owner":"bar", "value": 9}]'::jsonb);
1 rows affected
1 rows affected
1 rows affected
-- this explains why only one occurrence gets updated
WITH content_item AS (
SELECT
('{' || index - 1 || ',"owner"}')::TEXT[] AS path,
id,
(item -> 'owner') as owner
FROM view,
jsonb_array_elements(content) WITH ORDINALITY arr(item, index)
)
SELECT v.id, jsonb_set(content, content_item.path, '"foo1"')
FROM content_item
JOIN view v ON v.id = content_item.id
WHERE owner = '"foo"'
| id | jsonb_set |
|---|---|
| 0 | [{"owner": "foo1", "value": 1}, {"owner": "bar", "value": 2}, {"owner": "foo", "value": 3}] |
| 0 | [{"owner": "foo", "value": 1}, {"owner": "bar", "value": 2}, {"owner": "foo1", "value": 3}] |
| 1 | [{"owner": "bar", "value": 3}, {"owner": "bar", "value": 5}, {"owner": "foo1", "value": 6}] |
| 2 | [{"owner": "foo1", "value": 4}, {"owner": "foo", "value": 8}, {"owner": "bar", "value": 9}] |
| 2 | [{"owner": "foo", "value": 4}, {"owner": "foo1", "value": 8}, {"owner": "bar", "value": 9}] |
WITH RECURSIVE content_item AS (
SELECT ROW_NUMBER() OVER (PARTITION BY id ORDER BY index) AS index,
('{' || index - 1 || ',"owner"}')::TEXT[] AS path,
id,
(item -> 'owner') as owner
FROM view,
jsonb_array_elements(content) WITH ORDINALITY arr(item, index)
WHERE item->'owner' = '"foo"'
),
recursively AS
(
SELECT v.id, content_item.index, jsonb_set(content, content_item.path, '"foo1"') as content
FROM content_item
JOIN view v
ON content_item.id = v.id
WHERE content_item.index = 1
UNION ALL
SELECT rec.id, content_item.index, jsonb_set(content, content_item.path, '"foo1"') as content
FROM recursively rec
JOIN content_item
ON content_item.id = rec.id
AND content_item.index = rec.index+1
)
UPDATE view v
SET content = up.content
FROM recursively up
WHERE up.id = v.id
-- select the last of recursive iterations
AND index = (SELECT MAX(index) FROM recursively down WHERE up.id = down.id)
3 rows affected
select * from view
| id | content |
|---|---|
| 0 | [{"owner": "foo1", "value": 1}, {"owner": "bar", "value": 2}, {"owner": "foo1", "value": 3}] |
| 1 | [{"owner": "bar", "value": 3}, {"owner": "bar", "value": 5}, {"owner": "foo1", "value": 6}] |
| 2 | [{"owner": "foo1", "value": 4}, {"owner": "foo1", "value": 8}, {"owner": "bar", "value": 9}] |