PostgreSQL json(b) - Convert string to array & update field

Question

I have a jsonb field in PostgreSQL with the following content:

{ "object": { "urls": "A;B;C" } }

What I want to do is update the value of urls inside the object and transform the string with semicolon separated values into a JSON array. So the result should look like this:

{ "object" : { "urls": ["A", "B", "C"] } }

I found out how to get a JSON array. Using

to_json(string_to_array(replace((json->'object'->'urls')::text, '"',''), ';'));

gives me ["A", "B", "C"] (I think there should be a better way of doing it without the conversion json -> text -> array -> json. Suggestions are welcome)

But how do I update the urls field with the json array now? Probably I have to use jsonb_set?

Answer 1

Use jsonb and the function jsonb_set():

create table my_table(id int primary key, jdata jsonb);
insert into my_table values
(1, '{ "object": { "urls": "A;B;C" } }');

update my_table
set jdata = jsonb_set(
        jdata, 
        array['object', 'urls'], 
        to_jsonb(string_to_array(replace((jdata->'object'->'urls')::text, '"',''), ';'))
        )
returning *;

 id |                jdata                
----+-------------------------------------
  1 | {"object": {"urls": ["A", "B", "C"]}}
(1 row)