2

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?

Chris Stryczynski
  • 30,145
  • 48
  • 175
  • 286

1 Answers1

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;
Abelisto
  • 14,826
  • 2
  • 33
  • 41
  • 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 Jul 09 '17 at 15:32
  • It does work with `from (select values::json from temp_json) a;` though. – Chris Stryczynski Jul 09 '17 at 15:33
  • @ChrisStryczynski See my example: `values::json->>'annotations'` By "direct" I mean "without additional string processing like `replace`". – Abelisto Jul 09 '17 at 15:34
  • 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! – Chris Stryczynski Jul 09 '17 at 15:39