[FIXED] How to update a jsonb column with a replaced value in pgAdmin?

Issue

I have a PostgreSQL table called files which includes a jsonb table called formats. While some rows are [null], others have objects with this structure:

{
  "thumbnail": {
    "ext": ".jpg",
    "url": "https://some-url.com/image01.jpg",
    "name": "image01.jpg",
    //...other properties
  }
}

For every row I want to update the thumbnail.url and replace some-url with other-url.

I'm far from being an expert in PostgreSQL (or any other DB for that matter), and after some reading I tried to run the following query in pgAdmin:

UPDATE files 
  SET formats = jsonb_set(formats, '{thumbnail.url}', REPLACE('{thumbnail.url}', 'some-url', 'other-url'))

And I received this error: function jsonb_set(jsonb, unknown, text) does not exist

I tried to set format jsonb_set(formats::jsonb...), tried to target '{thumbnail}' instead of '{thumbnail.url}' - always the same error.

What am I doing wrong? Or is pgAdmin really doesn't support this function? How can I do such an update with pgAdmin query tool?

Solution

We can try to use ->> to get JSON content value of url and then replace your expect value from that.

Because your url field of your JSON might be string type we need to use " to content it before cast as JSONB

jsonb_set(target jsonb, path text[], new_value jsonb [, create_missing boolean])

UPDATE files 
SET formats = jsonb_set(formats, '{thumbnail,url}', CONCAT('"',REPLACE(formats->'thumbnail'->>'url','some-url','other-url'),'"')::JSONB);

sqlfiddle

Answered By - D-Shih

Answer Checked By - Marilyn (PHPFixing Volunteer)