Question 89985 in PostgreSQL Question posted in
The official documentation can be found here.

Update json string column with proper json in Postgres SQL – Postgresql

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?

Tags:

2

Answers


  1. 0

    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

    Login or Signup to reply.
  2. 0

    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}
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search