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?
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.