I have a column which type is JSON but it contains JSON strings like this:

"{\"a\":1,\"b\":2,\"c\":3}"

I want to update the values in the column with proper JSON objects without the quotes and escapes like this:

{"a":1,"b":2,"c":3}

I've tried the following statement even tough it says it does update rows, the columns are still the same.

UPDATE table SET column = to_json(column);

It seems like the to_json function doesn't work since it is a JSON string?

How can I update those values?

CodePudding user response：

You could cast the JSON column as text, remove the unwanted quotes and escapes, and then cast the resulting text as JSON.

update tbl
set js = trim(replace(js::text, '\',''), '"')::json 

demo

CodePudding user response：

You can use the #>> operator to extract the string and then convert it back to JSON with ::json:

UPDATE your_table
SET your_column = (your_column #>> '{}')::json;

A fully working demo:

create table your_table (
    your_column json
);

insert into your_table (your_column) values ('"{\"a\":1,\"b\":2,\"c\":3}"'::json);

select your_column, your_column #>> '{}'
from your_table ;

update your_table
set your_column = (your_column #>> '{}')::json;

select *
from your_table;