I have table like this:
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);
I would like to replate the value of "owner" in every array element with value "foo1" if an orignal value is "foo", everything else should stay intact.
I end up with update query like this:
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)
)
UPDATE view v
SET content = jsonb_set(content, content_item.path, '"foo1"')
FROM content_item
WHERE owner = '"foo"' AND content_item.id = v.id;
Problem is that only a first occurrence of "foo" is replaced with "foo1":
id |content
------------------------------------------------------------------------------------------------
0 |[{"owner": "foo1", "value": 1}, {"owner": "bar", "value": 2}, {"owner": "foo", "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}]
How do i replace all occurrences within an array?
Once you convert your UPDATE into a SELECT, the issue becomes obvious - for each occurrence of owner = 'foo' you get a row, and then you replace that occurrence, but the other one remains the same.
As UPDATE executes for each of the rows, it will update the rows with n occurrences n times, each time leaving n-1 occurrences intact.
There might be a better way, but what you can do is merge all these updates recursively, one after another, and then at the end you'll get the final version, and you use that version for the update.
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)
So in the first CTE the addition is I generate an index for each of the occurrences of "foo" as the owner using ROW_NUMBER(). This gives us a nice little breakdown of all the occurrences that we need to change. Then later in the recursively CTE, I apply each of the changes one by one, starting from index = 1 (first occurrence of "foo"). Eventually at the end I use the last (correlated subquery to get MAX(index) for each id) to execute the UPDATE.
I am fairly convinced that there should be a simpler and more elegant way to do it and will happily upvote the answer that provides one. Maybe you'll find my answer useful in the meantime.