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.
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)
replace(jsonb_build_array(c)::text,E'\"',''),replace(jsonb_build_array(c)::text,'\"','')::json
If you want to do this with native JSON functions, there are a couple of ways out. The simplest (for this particular use case) is probably jsonb_array_elements_text:
SELECT jsonb_array_elements_text(j.a) AS t
FROM (
SELECT jsonb_build_array(
'one "quoted string"',
'another "quoted string"'
) AS a
) j
;
If what you have isn't an array but rather individual JSON strings (whether from an array or from something else -- what's below is obviously a contrived example), you instead can use json_value, which takes a path expression and returns a scalar:
SELECT json_value(j2.elem, '$' RETURNING TEXT) t
FROM (
SELECT jsonb_array_elements(j1.a) AS elem
FROM (
SELECT jsonb_build_array(
'one "quoted string"',
'another "quoted string"'
) AS a
) j1
) j2
;
Both of these will get you:
t
-------------------------
one "quoted string"
another "quoted string"
(2 rows)
