0

I have a JSON file, unfortunatelly contains one if the value for formatting additional double quotes. So i try to read the file first as a text object.

How can i escape the double quotes within the values:

create table test_txt (exif text);
insert into test_txt values ('{"id": "1234", "name": "this is "my" Name", "adress": "12345 City "of" test"}');
Select exif::json from test_txt;

If i want to change the text object in a json object.

I get the file with curl from a url. Is there a difference between curl in cmd console

curl "url" -o c:\tmp\test.json

and

copy tablename ("columnname") from program 'curl "url"' in postgres
Tibor
  • 159
  • 6
  • The correct JSON escaping is with the backslash escape character, `"12345 City \"of\" test"`. But I guess with `COPY FROM` you also will run into escaping issues from the `COPY` serialisation format, which will need its own escaping – Bergi Jun 20 '22 at 22:06
  • I suspect that the COPY FROM curl ... ignore or delete this information in the JSON string. If i look the file after curl in cmd i see the escape charaters \n and \" but if i copy within postgresql from in that case missing the \ character and i see only " so after that i can not to convert in ::json. – Tibor Jun 21 '22 at 06:19

3 Answers3

0

I think this might help you:

How can I escape a double quote inside double quotes?

Basically use the \" to add a cuote inside a cuotes.

Luis Edwards
  • 73
  • 1
  • 8
0
select '{"name": "this is \"my\" Name"}' ::json;
jian
  • 4,119
  • 1
  • 17
  • 32
0

I solved the problem in following steps:

curl "https://myJsonUrl.com -o input.json

tr -d '\n' < input.json > output.json

create unlogged table "myJson" ("getJson" text);

copy "myJson" ("getJson") FROM 'C:\myFolder\output.json' csv quote e'\x01' delimiter e'\x02';
buddemat
  • 4,552
  • 14
  • 29
  • 49
Tibor
  • 159
  • 6