Loading JSON into Postgres

Today I had to load some JSON data into Postgres.

Postgres' COPY command it expects one JSON object per line rather than a full array.

For example, instead of a JSON array:

[
    { "id": 1, "name": "John Conway" },
    { "id": 2, "name": "Richard Feynman" },
]

It needs to be this format:

{ "id": 1, "name": "John Conway" }
{ "id": 2, "name": "Richard Feynman" }

It took me a surprisingly long time to get the data into Postgres, but the solution was fairly simple.

Here's how I did it.

Convert the data

This is done with one command:

cat input.json | jq -cr '.[]' | sed 's/\\[tn]//g' > output.json

Explanation

1. cat input.json - read the contents of the file
2. | jq -cr '.[]' - pipe JSON into jq and split it onto every line
3. | sed 's/\\[tn]//g' - [optional] remove tabs, newlines etc
4. > output.json - output to a new file

Importing the data

From here it's easiest to ingest the data into a JSONB column.

## Create a table
psql -h localhost -p 5432 postgres -U postgres -c "CREATE TABLE temp (data jsonb);"

## Copy your data in
cat output.json | psql -h localhost -p 5432 postgres -U postgres -c "COPY temp (data) FROM STDIN;"

That's it

20 seconds of reading, and 1 hour of my time. To get the data out of the table now you can use any of Postgres' amazing JSON support. For example:

select data->>'name'
from temp;

-- John Conway
-- Richard Feynman

Enjoy.

Comments

[affluent-bilby-classifieds]

Thanks for this Copple. I have signed up my project to the Supabase.io alpha program. I followed the instructions here. Can I please have the additional instructions to import the file into the "CREATE TABLE" page in Supabase? Thank you.

[Copple]

Hey! This is actually specific to Postgres, not the Supabase interface. We are building easy ways to load data in Supabase, but they aren't finished yet.

You can use the method i describe in this article to load directly into the Postgres database we give you - you will need to connect to it directly using psql. Do you have psql on your computer?

[affluent-bilby-classifieds]

Hi Copple.

Yes, I am running postgres (psql 9.6.18) in a docker container. I have successfully followed your instructions. It was after that I read about supabase and said Eureka!

I was going to try importing it today. I exported it first.
I used: "pg_dump -d menudb -U fruty -t menu > file.sql" but I haven't tried importing that into supabase as yet. I can put the contents in a codepen if you like.

I am very interested in directly importing it to the supabase Postgres database if you can advise how I should proceed.

Thank you

[Copple]

You can import it exactly as you do it on the local machine! The key difference is that you need to change the connection parameters. So instead of the local host:

psql -h localhost -p 5432 postgres -U postgres

You use the connection details for the Supabase database

PGPASSWORD=[your password] psql -h XXX.supabase.co -p 5432 postgres -U postgres 

You can find the connection details in the "settings" page of your project in Supabase

[affluent-bilby-classifieds]

That's fantastic! Thanks Copple. I will do that tomorrow morning.

[affluent-bilby-classifieds]

psql -h XXXX.supabase.co -p 5432 postgres -U postgres -c "CREATE TABLE menudb (data jsonb);"
Password for user postgres:
CREATE TABLE
root@45279c35af82:/init/json# cat output.json | psql -h XXXX.supabase.co -p 5432 postgres -U postgres -c "COPY menudb (data) FROM STDIN;"
Password for user postgres:
COPY 34

[affluent-bilby-classifieds]

Looks like it's worked!

Thank you!

[Copple]

Woohoo! I'm impressed you pursued this all the way to the end. If you need help with anything else, feel free to reach my cofounder and I on [e-mail]. Would love to hear more about what you're building

[affluent-bilby-classifieds]

The next thing I need to find out is how to use it as a datasource (restful API I think it's called) for react-admin. Is there a similar page to the "connection details" that gives me the path information I need to enter into my react-admin code related to data provider (currently using ra-data-simple-rest) configuration?

Admin dataProvider={simpleRestProvider('http://path.to.my.api/')

edit: I just saw your response.

should I remove my next question and e-mail instead?

[Copple]

I've never used React Admin, so I don't know exactly how it would work, but I imagine you can go to the "Docs" section in your dashboard and use the "Bash" commands to "cURL" the data 😂. What a mouthful

Alternatively, we have a table view, which is getting better every week. (go to the "Zap" icon, then toggle at the top between Table/SQL). We have some huge improvements coming this week, and eventually you won't even need React Admin - we are building it for you :). If you've ever used Airtable, this is how our Table View will function

[affluent-bilby-classifieds]

Thanks Copple.

If I can "cURL" I can probably use axios or fetch for CRUD. I will check out the docs and link this thread to our dev.
We do intend to use React-Admin and it will become a dashboard for the business.