By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
create table document_revisions (data jsonb);
insert into document_revisions values ('{"id": "doc-1f73vuahm0", "content": {"img": {"url": "https://my.oldserver.com/api/v1/images/covid-19-m-rna-vaccines.png?id=MQkvMjAyMS82LzEvZWI2YzQwZjItMjYzMS00MTY4LWFhZGQtNjZjMmVkOTg5ZDQ2LnBuZwk4MTQ%3D&auto=format", "width": 1390, "height": 341, "mediaId": "3nO-shKnBV18", "mimeType": "image/png", "originalUrl": "https://my.oldserver.com/api/v1/images/covid-19-m-rna-vaccines.png?id=MQkvMjAyMS82LzEvZWI2YzQwZjItMjYzMS00MTY4LWFhZGQtNjZjMmVkOTg5ZDQ2LnBuZwk4MTQ=", "originalUrl1": "https://my.oldserver.com/api/v1/images/covid-19-m-rna-vaccines.png?id="}, "source": "Source: Example1", "caption": "COVID-19 mRNA vaccines"}, "identifier": "p:814:794.image"}');
CREATE TABLE
INSERT 0 1
update document_revisions set data = regexp_replace(data::text,
'(https\://my\.)oldserver(\.com/api/v1/images/[\w\-]+\.[a-z]+\?id\=(?:[^"]+)*)',
'\1newserver\2', 'g')::jsonb
UPDATE 1
select jsonb_pretty(data) from document_revisions
| jsonb_pretty |
|---|
| { "id": "doc-1f73vuahm0", "content": { "img": { "url": "https://my.newserver.com/api/v1/images/covid-19-m-rna-vaccines.png?id=MQkvMjAyMS82LzEvZWI2YzQwZjItMjYzMS00MTY4LWFhZGQtNjZjMmVkOTg5ZDQ2LnBuZwk4MTQ%3D&auto=format", "width": 1390, "height": 341, "mediaId": "3nO-shKnBV18", "mimeType": "image/png", "originalUrl": "https://my.newserver.com/api/v1/images/covid-19-m-rna-vaccines.png?id=MQkvMjAyMS82LzEvZWI2YzQwZjItMjYzMS00MTY4LWFhZGQtNjZjMmVkOTg5ZDQ2LnBuZwk4MTQ=", "originalUrl1": "https://my.newserver.com/api/v1/images/covid-19-m-rna-vaccines.png?id=" }, "source": "Source: Example1", "caption": "COVID-19 mRNA vaccines" }, "identifier": "p:814:794.image" } |
SELECT 1