How to update values ​​in a table column?

Question

PostgreSQL

ExternalWorkItems - table, ExternalUrls - column in the table
ExternalUrls (jsonb) contains values ​​of the format [{"Url": " https://test/browse/CP-12 ", "Updated": "2020-12-15 15:57 :45", "Comments": "", "JiraRemoteLinkId": 25628}]
How to bulk update rows in this column by replacing " https://test/browse/CP-12 " with " https://example/browse/ CP-12 "?
Cut off the first N characters in json and replace them with your value?
Tried:

update public."ExternalWorkItems" as ew
SET ew."ExternalUrls" = overlay (ew."ExternalUrls" placing 'http://test' from 1 for 30);

Mistake:

SQL Error [42883]: ERROR: function pg_catalog.overlay(jsonb, unknown, integer, integer) does not exist
  Подсказка: No function matches the given name and argument types. You might need to add explicit type casts.

Answer 1

There are different options.
You can work with JSONB in ​​a human way :
the jsonb_set() function changes the value along the path inside the JSON. In your case something like this:

jsonb_set(ExternalUrls, '{0,Url}', '"https://example/browse/CP-12"')

- will replace the Url key in the element with index 0 of the array from ExternalUrls. It returns a new JSONB object, updating table columns in SQL is done via UPDATE anyway:

UPDATE ExternalWorkItems
   SET ExternalUrls = jsonb_set(ExternalUrls, '{0,Url}', '"https://example/browse/CP-12"');

There is some problem with jsonb_set changing one value i.e. you cannot use a "multi-path" of the type {*,Url}to change all the elements of the ExternalUrls array at once (if you have more than one of them there).
The problem is solved through a series of subrequests using the functions jsonb_array_elements() -> jsonb_set() -> jsonb_agg(), but some kind of monstrous construction comes out.
Also, if you need to replace part of a string (apparently the domain in the link) without touching the rest, things get even worse. Something like this:

UPDATE ExternalWorkItems
   SET ExternalUrls = jsonb_set(
          ExternalUrls, '{0,Url}',
          to_jsonb(replace(ExternalUrls #>> '{0,Url}', 'test', 'example'))
   );

(again, it will change only the first element of the ExternalUrls array)
Therefore, a small crutch is proposed in the form of converting JSONB to text and a simple replacement (with some risk, replacing the wrong thing ... but if you are sure of the structure, then this is not a problem):

UPDATE ExternalWorkItems SET ExternalUrls = replace(ExternalUrls::text, 'https://test', 'https://example')::jsonb;