5

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?

Anton Zvonovsky
  • 313
  • 2
  • 6
  • 16
  • I don't get it. You already *have* json in your initial variable. Why call `to_json()` on it? – mabi Nov 18 '14 at 15:43
  • My initial jsonResult variable is of type varchar and is built by several concatenations and I need to insert it into a column with type JSON. Sorry, haven't mentioned it. – Anton Zvonovsky Nov 18 '14 at 15:54

2 Answers2

11

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.

Community
  • 1
  • 1
mabi
  • 5,279
  • 2
  • 43
  • 78
  • Yes, I've already tried to do so. Nothing happens... I used `raise notice` to check the contents and it raises nothing... – Anton Zvonovsky Nov 18 '14 at 16:09
  • This is the full contents of `jsonResult` variable I'm trying to save: `NOTICE: qwe,[{"key":{04949429911,"Code":"400"},"value":"20000.00"},{"key":{"InsuranceNumber":"04949429911","Code":"403"},"value":"10000.00"},{"key":{"InsuranceNumber":"05548560177","Code":"400"},"value":"20000.00"},{"key":{"InsuranceNumber":"05548560177","Code":"403"},"value":"10000.00"}]` – Anton Zvonovsky Nov 18 '14 at 16:13
  • 1
    Now that you said it.. If you add the missing key, does it insert okay? – mabi Nov 18 '14 at 16:20
  • Terribly sorry for wasting your time... Fixed generation of json string and it updated everything as it should using `jsonResult::json`. – Anton Zvonovsky Nov 18 '14 at 16:24
  • No worries, I haven't noticed the missing key, either. – mabi Nov 18 '14 at 16:42
  • Best answer so far, tried so many things but simply appending ::json worked! – radkan Sep 20 '21 at 22:16
2

I needed to convert a bytea column to jsonb and I ended up with escaped characters when using to_json. I also used bytea_data_column::jsonb and postgres said cannot cast to jsonb. Here is how I worked around it:

bytea_data_column::text::jsonb

Steve
  • 396
  • 1
  • 5
  • 8