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?

klin · Accepted Answer · 2017-01-05 10:15:39Z

5

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)

answered Jan 5, 2017 at 10:15

klin

123k15 gold badges240 silver badges262 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

mxlse Over a year ago

It worked well. I just had to add WHERE jdata->'object'->'urls' IS NOT NULL. Otherwise those rows had null as json (or jdata in your case).

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related