Update json string column with proper json in Postgres SQL

Question

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?

ahmed · Accepted Answer · 2023-01-26 09:04:15Z

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

Álvaro González · Accepted Answer · 2023-05-12 10:24:33Z

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 ;

your_column	?column?
`"{\"a\":1,\"b\":2,\"c\":3}"`	`{"a":1,"b":2,"c":3}`

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

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

Collectives™ on Stack Overflow

Update json string column with proper json in Postgres SQL

2 Answers 2

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Related