Update json in postgresql-CodePudding

I am not a regular user of Postgresql, I have read some documentation but I am afraid to run the wrong query and I do not want to damage a database in production.

I need to edit the url field of the next json:

server=> select * from parameter;
     name     |                 value                  
-------------- ----------------------------------------
 dataaccs_ftp | {\r                                    
              | "url": "url.url.com", \r 
              | "username": "xxxxxxx", \r            
              | "password": "xxxxxxx",\r            
              | "files": ["xxx.zip", "xxx.zip"]\r  
              | }
 fldr_folders | {\r                                    
              | "proc": "xxxxx", \r        
              | "done": "xxxxxx", \r        
              | "error": "xxxxxxx"\r        
              | }
(2 rows)

I need to know if the following query is correct to perform the task:


UPDATE parameter SET dataaccs_ftp = jsonb_set(dataaccs_ftp,'{"url":"newurl.url.com"}'

Thanks for the help.

Regards,

CodePudding user response：

The usage of jsonb_set looks entirely wrong here. The following should do what you need:

UPDATE parameter SET value = 
  jsonb_set(
      value,               -- existing value
      '{url}',             -- json path for update
      '"newurl.url.com"'   -- new value
  )
WHERE name = 'dataaccs_ftp'  -- which values to update
;

You want to update the value common so that is the left hand side of the assinment. jsonb_set will take the current value of value (1st param), look for the data at {url} (second param, a json path) and replace with the value in the last argument. Double quotes inside single quotes as it needs to be a json value.

https://dbfiddle.uk/?rdbms=postgres_14&fiddle=e6c36c23fc8414371557af63ec25f6b3

CodePudding user response：

CREATE TABLE json_update_test(yada jsonb) ;
INSERT INTO json_update_test (yada ) VALUES ('{"url":"newurl.url.com"}');
UPDATE json_update_test SET yada['url'] = '"example.com"';
SELECT * FROM json_update_test ;
          yada          
------------------------
 {"url": "example.com"}
(1 row)

notice that the example here accesses the object by reference. Also, the double quotes inside the single quotes make the string a valid jsonb object.

From this example (and coding blind here) your update would look like:

UPDATE parameter 
SET value['url'] = '"newurl.url.com"'
WHERE "name" = 'dataaccs_ftp';