Update part of a string from a jsonb column in postgres database

Question

I have to modify part of a link in a jsonb column from a postgres database.

The object looks like this:

{
  "large": {
    "ext": ".jpg",
    "url": "https://bucket1.s3.eu-central-1.amazonaws.com/large_ansgar_scheffold_IZZ_78_Ugqsow_unsplash_498662ba12.jpg",
    "hash": "large_ansgar_scheffold_IZZ_78_Ugqsow_unsplash_498662ba12",
    "mime": "image/jpeg",
    "name": "large_ansgar-scheffold-IZZ78Ugqsow-unsplash.jpg",
    "path": null,
    "size": 85.7,
    "width": 1000,
    "height": 667
  },
  "small": {
    "ext": ".jpg",
    "url": "https://bucket1.s3.eu-central-1.amazonaws.com/small_ansgar_scheffold_IZZ_78_Ugqsow_unsplash_498662ba12.jpg",
    "hash": "small_ansgar_scheffold_IZZ_78_Ugqsow_unsplash_498662ba12",
    "mime": "image/jpeg",
    "name": "small_ansgar-scheffold-IZZ78Ugqsow-unsplash.jpg",
    "path": null,
    "size": 24.44,
    "width": 500,
    "height": 333
  },
  "medium": {
    "ext": ".jpg",
    "url": "https://bucket1.s3.eu-central-1.amazonaws.com/medium_ansgar_scheffold_IZZ_78_Ugqsow_unsplash_498662ba12.jpg",
    "hash": "medium_ansgar_scheffold_IZZ_78_Ugqsow_unsplash_498662ba12",
    "mime": "image/jpeg",
    "name": "medium_ansgar-scheffold-IZZ78Ugqsow-unsplash.jpg",
    "path": null,
    "size": 50.9,
    "width": 750,
    "height": 500
  },
  "thumbnail": {
    "ext": ".jpg",
    "url": "https://bucket1.s3.eu-central-1.amazonaws.com/thumbnail_ansgar_scheffold_IZZ_78_Ugqsow_unsplash_498662ba12.jpg",
    "hash": "thumbnail_ansgar_scheffold_IZZ_78_Ugqsow_unsplash_498662ba12",
    "mime": "image/jpeg",
    "name": "thumbnail_ansgar-scheffold-IZZ78Ugqsow-unsplash.jpg",
    "path": null,
    "size": 6.88,
    "width": 234,
    "height": 156
  }
}

I want to change 'bucket1' from each url into 'bucket2'.

I can change the whole url like this:

UPDATE public.files 
SET formats = jsonb_set(formats, '{large,url}', '"new-url"')

But how can I change only part of it? Can I somehow combine jsonb_set with regexp_replace? I thought about converting it to string and back but I am not sure how I should do that.

relevant post: https://stackoverflow.com/questions/26703476/how-to-perform-update-operations-on-columns-of-type-jsonb-in-postgres-9-4 — jian, Jun 29 '22 at 14:23

score 0 · Answer 1 · answered Jun 29 '22 at 14:58

To update the URL for each parent key "large", "small", etc, you will want to run jsonb_set(js, '{<key>, url}'::text[], replace(...)::json) for each key, possibly nesting jsonb_set set calls in a single set clause in an update query. However, due to the fairly large number of keys to be updated in this example and possible future variability in the keys in each json object, you can use a recursive cte to update your json in-place:

with recursive cte(js, vals, c) as (
   select jsonb_set(t2.js, ('{'||(t2.vals ->> 0)::text||',url}')::text[], replace((t2.js -> (t2.vals ->> 0)::text -> 'url')::text, 'bucket1'::text, 'bucket2'::text)::jsonb), t2.vals, 1 
   from (select t.js, jsonb_agg(t1) vals from tbl t cross join jsonb_object_keys(t.js) t1 group by t.js) t2
   union all
   select jsonb_set(t2.js, ('{'||(t2.vals ->> t2.c)::text||',url}')::text[], replace((t2.js -> (t2.vals ->> t2.c)::text -> 'url')::text, 'bucket1'::text, 'bucket2'::text)::jsonb), t2.vals, t2.c+1
   from cte t2 where t2.c < jsonb_array_length(t2.vals)
)
select jsonb_pretty(t.js) from cte t where t.c = jsonb_array_length(t.vals)

See fiddle.

Update part of a string from a jsonb column in postgres database

1 Answers1