How can I import JSON data into PostgreSQL with psql?

Question

PostgreSQL has a lot of functions for working with JSON data. However, it seems to be difficult to import JSON data, since the COPY command does not support JSON.

Assuming I have a file named data.json that contains JSON data like the following:

[
  {"id":1, "name": "Jakob"},
  {"id":2, "name": "Klara"}
]

Is there a simple way to import that JSON data file just using psql?

If it's a one time project, I would use a JSON to CSV converter, then use the copy command per normal. stackoverflow.com/questions/4130849/… — Paulb
– Paulb, Commented Feb 26, 2017 at 14:15

Jakob Egger · Accepted Answer · 2017-02-26 14:04:29Z

1

You can use the CSV format to import arbitry data, as long as you quote it correctly. You can use cat & sed to quote your CSV file:

cat data.json | sed 's/"/""/g' | cat <(echo '"') - <(echo '"')

The first cat command reads the file, the sed command replaces all double quotes with two double quotes, and the final cat puts a double quote before and after the json.

Let's create a table on the server:

psql -c 'CREATE TABLE import(data jsonb)'

You can now use the result of this command with psql:

cat data.json | sed 's/"/""/g' | cat <(echo '"') - <(echo '"') | \
 psql -c 'COPY import(data) FROM STDIN (FORMAT CSV)'

answered Feb 26, 2017 at 14:04

Jakob Egger

12.1k4 gold badges42 silver badges48 bronze badges

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

Collectives™ on Stack Overflow

How can I import JSON data into PostgreSQL with psql?

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related