Replace key/value pair in all json array elements based on a certain condition

user_12506072 0
Viewed 317

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?

jsonb postgresql sql
2 Answers

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.

Here's a working demo on dbfiddle.

user_31229210

Use a text-based approach:

update view set
content = replace(content::text, '"owner": "foo"', '"owner": "foo1"')::json

See live demo.

Note: Postgres doesn’t support casting from text straight to jsonb, but it will cast text to json then auto-cast to jsonb during the update.

Also a style thing: If possible, it’s best to not use reserved words, eg view, for entity names.

user_2561960
Related