I have a jsonb field in PostgreSQL with the following content:
{ "object": { "urls": "A;B;C" } }
What I want to do isupdate the value ofurls 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 itwithout the conversion json -> text -> array -> json. Suggestions are welcome)
But how do I update theurls field with the json array now? Probably I have to usejsonb_set?
1 Answer1
Usejsonb and the functionjsonb_set():
create table my_table(id int primary key, jdata jsonb);insert into my_table values(1, '{ "object": { "urls": "A;B;C" } }');update my_tableset 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)
1 Comment
WHERE jdata->'object'->'urls' IS NOT NULL. Otherwise those rows had null as json (or jdata in your case).Explore related questions
See similar questions with these tags.