Insert JSON into PostgreSQL that contains quotation marks

Question

I'm trying to import a JSON file into a table. I'm using the solution mentioned here: https://stackoverflow.com/a/33130304/1663462:

create temporary table temp_json (values text) on commit drop;
copy temp_json from 'data.json';

select
  values->>'annotations' as annotationstext

from   (
           select json_array_elements(replace(values,'\','\\')::json) as values
           from   temp_json
       ) a;

Json file content is:

{"annotations": "<?xml version=\"1.0\"?>"}

I have verified that this is a valid JSON file.

The json file contains a \" which I presume is responsible for the following error:

CREATE TABLE
COPY 1
psql:insertJson2.sql:13: ERROR:  invalid input syntax for type json
DETAIL:  Expected "," or "}", but found "1.0".
CONTEXT:  JSON data, line 1: {"annotations": "<?xml version="1.0...

Are there any additional characters that need to be escaped?

Abelisto · Accepted Answer · 2017-07-09 15:25:26Z

4

Because copy command processes escape ('\') characters for text format without any options there are two ways to import such data.

1) Process file using external utility via copy ... from program, for example using sed:

copy temp_json from program 'sed -e ''s/\\/\\\\/g'' data.json';

It will replace all backslashes to doubled backslashes, which will be converted back to single ones by copy.

2) Use csv import:

copy temp_json from 'data.json' with (format csv, quote '|', delimiter E'\t');

Here you should to set quote and delimiter characters such that it does not occur anywhere in your file.

And after that just use direct conversion:

select values::json->>'annotations' as annotationstext from temp_json;

answered Jul 9, 2017 at 15:25

Abelisto

15.8k3 gold badges38 silver badges47 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Chris Stryczynski Over a year ago

I don't think I can use the direct conversion mentioned. It gives an error of ` ERROR: operator does not exist: text ->> unknown LINE 2: values->>'annotations' as annotations ^ HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts.`

Chris Stryczynski Over a year ago

It does work with from (select values::json from temp_json) a; though.

Abelisto Over a year ago

@ChrisStryczynski See my example: values::json->>'annotations' By "direct" I mean "without additional string processing like replace".

Chris Stryczynski Over a year ago

If I change the temp table definition to create temporary table temp_json (values jsonb) on commit drop; it works directly. Really happy with this elegant solution!

Collectives™ on Stack Overflow

Insert JSON into PostgreSQL that contains quotation marks

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related