1

I have hundreds of json files in the format:

{"name": "example", "location": "example"}
{"name": "example2", "location": "example2"}

Each line is a different json object and I have all the json files stored locally on a Linux machine.

The formatting of these jsons have caused many problems when trying to import the data. First of all, all of the strings are written using double quotes. I read that double quotes are supposed to be used for column names so this may be an issue. Second of all, the main issue is some of the strings in the json files objects with key-values like:

{"example1": "\"example text\""}

or

{"example2": "that's"}

The query I am running to test is:

COPY table(data jsonb) from '/path/to/jsons.json';

In the case of the first example, it does not read the backslash and this error is output:

ERROR:  invalid input syntax for type json
DETAIL:  Token "example" is invalid.
CONTEXT:  JSON data, line 1: "example":""exampl...

In the case of the second example, it treats the single quotation as the end of the string and hence results in another syntax error:

ERROR: syntax error at or near "s"
LINE 1: "example":"that's"
                        ^

Reformatting the json files is not really an option because I have hundreds of files, each with hundreds of thousands of objects within them, unless there is an efficient way to do so.

I was wondering if there is a way to make PostgreSQL accept the format of my jsons and properly insert each object into the table. I would like to keep the data type as json or jsonb so I can make use of PostgreSQL's json functions later.

Thanks!

fraoudas
  • 23
  • 5
  • It looks like PostgreSQL is quite specific about the format it accepts for jsonb. You could try the json type or I'd look at creating a python script to iterate through your files. – Ben Trewern Mar 17 '21 at 19:30
  • 1
    Must the storage format be jsonb? It's not difficult to massage JSON to a SQL insert using tools like Vim and jq. – Elias Toivanen Mar 17 '21 at 19:33
  • 1
    I would use jq to convert the json to csv. Then import the csv. – dmg Mar 17 '21 at 20:34

0 Answers0