Postgres 14

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

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}]

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}]