0

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?

Jakob Egger
  • 11,981
  • 4
  • 38
  • 48
  • If it's a one time project, I would use a JSON to CSV converter, then use the copy command per normal. http://stackoverflow.com/questions/4130849/convert-json-format-to-csv-format-for-ms-excel – Paulb Feb 26 '17 at 14:15

1 Answers1

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)'
Jakob Egger
  • 11,981
  • 4
  • 38
  • 48