Postgres query to convert stringifyed json object to jsonb object

1
votes

let say that the json object is > { "foo": "bar"} after stringyfing i got > "{ \"foo\": \"bar\" }"

how can I get back the orginal json object using UPDATE sql query?

enter image description here

i'm aware of that it's a bad DB architecture it was designed by another engineer before me, that's why I would like get back the original json data and then alter the column to jsonb

Update: please be aware that I'm looking for an answer to do that with only sql query and without any involving of programming languages like javascript.. etc

2
sqljsonpostgresql

2 Answers

1
votes

I was able to solve this same issue by doing some regexp replaces. You might not need the where clause, but in my case, I had a bug that started to stringify the JSONB column so only some of my data needed this change applied. 

update your_table 
    set text = 
        jsonb(regexp_replace(regexp_replace(regexp_replace(text::text, '"$', ''),  '^"+', ''), '\\"', '"', 'g')) 
    where text->>'foo' is null;
-2
votes

You could do to_json('{ \"foo\": \"bar\" }'::text)

so it would be something like

update yourtable set yourjsoncolumn = to_json(yourjsoncolum::text)