Update property of object in jsonb array and keep other properties-CodePudding

I have a postgres table with a jsonb colum like this:

create table if not exists doc
(
  id uuid not null
    constraint pkey_doc_id
    primary key,
  data jsonb not null
);

INSERT INTO doc (id, data) VALUES ('3cf40366-ea58-402d-b63b-c9d6fdf99ec8', '{"Id": "3cf40366-ea58-402d-b63b-c9d6fdf99ec8", "Tags": [{"Key": "inoivce", "Value": "70086"},{"Key": "customer", "Value": "100233"}] }' );

INSERT INTO doc (id, data) VALUES ('ae2d1119-adb9-41d2-96e9-53445eaf97ab', '{"Id": "ae2d1119-adb9-41d2-96e9-53445eaf97ab", "Tags": [{"Key": "project", "Value": "12345"},{"Key": "customer", "Value": "100233"}]}' );b9-41d2-96e9-53445eaf97ab", "Tags": [{"Key": "customer", "Value": "100233"}]}' )

Tags.Key in the first row contains a typo inoivce which I want to fix to invoice:

{
  "Id": "3cf40366-ea58-402d-b63b-c9d6fdf99ec8", 
  "Tags": [{
    "Key": "inoivce", 
    "Value": "70086"
  },{
    "Key": "customer", 
    "Value": "100233"
  }]
}

I tried this:

update doc set data = jsonb_set(
  data,
  '{"Tags"}',
  $${"Key":"invoice"}$$
) where data @> '{"Tags": [{ "Key":"inoivce"}]}';

The typo gets fixed but I'm loosing the other Tags elements in the array:

{
  "Id": "3cf40366-ea58-402d-b63b-c9d6fdf99ec8", 
  "Tags": [{"Key": "invoice"}]
}

How can I fix the typo without removing the other elements of the Tags array?

Dbfiddle for repro.

CodePudding user response：

One possible solution, not so obvious : we need a CTE because the idea here is to loop on the 'Tags' jsonb array elements using the jsonb_agg aggregate function to rebuild the array, but the SET clause of an UPDATE doesn't accept aggregate functions ...

WITH list AS
(  SELECT d.id, (d.data - 'Tags') || jsonb_build_object('Tags', jsonb_agg(jsonb_set(e.content, '{Key}' :: text[], to_jsonb(replace(e.content->>'Key', 'inoivce', 'invoice'))) ORDER BY e.id)) AS data
     FROM doc AS d
    CROSS JOIN LATERAL jsonb_array_elements(d.data->'Tags') WITH ORDINALITY AS e(content, id)
    WHERE d.data @? '$.Tags[*] ? (exists(@ ? (@.Key == "inoivce")))'
    GROUP BY d.id, d.data
)
UPDATE doc AS d
   SET data = l.data
  FROM list AS l
 WHERE d.id = l.id

see the result in dbfiddle