Importing JSON into postgresql invalid input syntax for type json

Question

I'm trying to import some data into a PostgreSQL database. The data is stored in a JSON file (file.json) that looks like this:

[
        {
         "key1": 12,
         "key2": 2
        },
        {
         "key1": 151,
         "key2": 9
        }
]

I'm using psql and the instructions are listed here. The table that JSON data will be imported in is created:

create table table_import (doc json);

While copying data into the table

\copy table_import from '/location/file.json'

I get the following error:

ERROR:  invalid input syntax for type json
DETAIL:  Token "" is invalid.
CONTEXT:  JSON data, line 1: ...
COPY table_import, line 1, column doc: "["

Since I'm doing exactly what the instructions in the answer are, I'm starting a new thread.

When importing a text file, each line in the file is one row to be inserted. You need to write the JSON into a single line in the input file. — user330315
– user330315, Commented Mar 21, 2019 at 16:24
You can prepare the file with stedolan.github.io/jq/manual as follows: jq -c '.[]' <file>.json > <new-file>.json (the -c creates a compact format without line breaks). Then you can import the <new-file>.json — Ancoron
– Ancoron, Commented Mar 21, 2019 at 17:02

zer0mode · Accepted Answer · 2019-12-05 16:43:10Z

1

In the example you're referring to the table is populated directly by INSER INTO and VALUES and later treated as array of JSON objects.

When importing with \COPY table FROM file.json each JSON object should be on one line. This would be your file.json:

{"key1": 12,"key2": 2}
{"key1": 151,"key2": 9}

See the last post on the page you're referring to or the beginning of my post.

answered Dec 5, 2019 at 15:42

zer0mode

1313 bronze badges

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

1 Answer 1