Remove double quotes from json object in Postgres

Question

I have a Postgres function with an insert statement in which one of the columns has to be converted to json and inserted into the table. Here, I want to remove the double-quotes that enclose the json object.

Please help on how to achieve it.

REPLACE(json_build_array(adm_id),'"', '') 

The above code errors out saying replace(json, text, text) doesn't exist. I tried to cast it to text and remove the double-quotes, but when I convert it back to json, it again adds double-quotes.

Answer 1

Try using trim to remove the double quotes around the json document:

jsonb_build_array(trim('"' FROM adm_id))

or replace to remove them from every json element:

replace(jsonb_build_array(c)::text,'\"','')

Demo:

WITH j (c) AS (
  VALUES ('"{id: 1, txt: "foo"}"'),('{id: 2}')
) 
SELECT 
  jsonb_build_array(trim('"' FROM c)),
  replace(jsonb_build_array(c)::text,'"','')
FROM j;

     jsonb_build_array     |         replace         
---------------------------+-------------------------
 ["{id: 1, txt: \"foo\"}"] | [\{id: 1, txt: \foo\}\]
 ["{id: 2}"]               | [{id: 2}]
(2 rows)