How to update a jsonb column with a replaced value in pgAdmin?-CodePudding

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?

CodePudding user response：

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

CodePudding user response：

The second parameter of jsonb_set() must be an array with one array element for each "path" element. So the second parameter should be '{thumbnail,url}' or more obvious: array['thumbnail', 'url']

And the third parameter must be a jsonb value, but replace returns a text, so you need to use e.g. to_jsonb() to convert the result of the replace() to a jsonb value.

And as D-Shih pointed out, you need to extract the old value using ->>. But to get the URL you need to "navigate" to it: formats -> 'thumbnail ->> 'url'

I would also add a WHERE clause so that you only update rows that actually contain a URL.

UPDATE files 
  SET formats = jsonb_set(formats, 
                          '{thumbnail,url}', 
                          to_jsonb(replace(formats -> 'thumbnail' ->> 'url', 'some-url', 'other-url'))
                          )
where (formats -> 'thumbnail') ? 'url'