postgresql to_json() function escapes all doublequote characters

Question

I've written a plpgsql script which generates an array of json objects in a string but after I use to_json() method passing a variable with that string to it, it returns a result which is doublequoted and also every doublequote character is escaped. But I need that string as is.

initial content of jsonResult variable is:

[{"key":{04949429911,"Code":"400"},"value":"20000.00"},{"key":{"InsuranceNumber":"04949429911","Code":"403"},"value":"10000.00"},...]

but after to_json() it looks like this:

"[{\"key\":{04949429911,\"Code\":\"400\"},\"value\":\"20000.00\"},{\"key\":{\"InsuranceNumber\":\"04949429911\",\"Code\":\"403\"},\"value\":\"10000.00\"}...]"

This is the place where everything stored in jsonResult breakes:

UPDATE factor_value SET params = to_json(jsonResult) WHERE id = _id;  

What am I doing wrong?

Answer 1

This answer points out that simply casting to json should suffice:

UPDATE factor_value SET params = jsonResult::json WHERE id = _id;

The weird escaping you see is probably due to postgresql not realizing you already have valid JSON and your varchar is just converted to a plain javascript/JSON string.