How to Import a Json File into PostgreSQL Database Cluster
Introduction
In this article we’ll be covering how to import a json file into a PostgreSQL database cluster. The prerequisites are very minimal and if you have PostgreSQL installed you can jump right into the content.
Prerequisites
- PostgreSQL database cluster must be installed and properly working in your device. Execute the command
psql -V
in your terminal to view and display the version number of the PostgreSQL.
Access the PostgreSQL command line interface
Before we proceed to the importing of a json file into a PostgreSQL table, we must make sure have an access to the interactive terminal of PostgreSQL database cluster.
To enter the psql
, you must execute the bash command :
1 | sudo -u postgres psql |
After filling the required password, you are automatically connected to a postgres database and superuser.
Create a database and table in PostgreSQL
To create a database in PostgreSQL, execute the command below :
1 | CREATE DATABASE somedb; |
Enter the database by using the command \c
followed by the database name.
To create a table in PostgreSQL for us to import a json file into it. The structure of the tables must be assumed like this.
1 2 3 4 5 6 | CREATE TABLE contacts( id INT NOT NULL PRIMARY KEY, name VARCHAR(50), age INT NOT NULL, active VARCHAR(10) ); |
Standard PostgreSQL command to extract information as json and insert into a table
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 | WITH contacts_json (doc) AS (VALUES( '[ { "id": 1, "name": "Jimmy", "age": 25, "active": "yes" }, { "id": 2, "name": "Annie", "age": 22, "active": "yes" }, { "id": 3, "name": "John", "age": 21, "active": "no" }, { "id": 4, "name": "Mark", "age": 15, "active": "no" }, { "id": 5, "name": "Shelly", "age": 20, "active": "yes" } ]'::json)) INSERT INTO contacts (id, name, age, active) SELECT p.* FROM contacts_json l CROSS JOIN lateral json_populate_recordset(NULL::contacts, doc) AS p ON conflict (id) do UPDATE SET name = excluded.name, active = excluded.active; |
This query will insert a value using the json_populate_recordset(null::contacts, doc)
and will generate a JSON object in the PostgreSQL table, then execute the command SELECT * FROM contacts
to display the value of rows in the table.
1 2 3 4 5 6 7 | id | name | age | active ----+--------+-----+-------- 1 | Jimmy | 25 | yes 2 | Annie | 22 | yes 3 | John | 21 | no 4 | Mark | 15 | no 5 | Shelly | 20 | yes |
Importing json file into PostgreSQL
Let’s assume that we have a json file that we need to import the json objects into the PostgreSQL table.
contacts.json
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 | [ { "id": 6, "name": "Jade", "age": 30, "active": "yes" }, { "id": 7, "name": "May", "age": 21, "active": "yes" }, { "id": 8, "name": "Teresita", "age": 25, "active": "no" }, { "id": 9, "name": "Lesley", "age": 26, "active": "no" }, { "id": 10, "name": "Ian", "age": 27, "active": "yes" } ] |
Create a temporary table :
1 | CREATE TEMP TABLE new_contacts(info json); |
Load the json file into a temporary table in PostgreSQL using the following syntax :
1 2 | \SET content `cat /home/linux/Desktop/contacts.json` INSERT INTO new_contacts VALUES(:'content'); |
Then use the SELECT * FROM new_contacts
statement to display the json object value inside the temporary table in PostgreSQL.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 | info ----------------------------- [ + { + "id": 6, + "name": "Jade", + "age": 30, + "active": "yes" + }, + { + "id": 7, + "name": "May", + "age": 21, + "active": "yes" + }, + { + "id": 8, + "name": "Teresita",+ "age": 25, + "active": "no" + }, + { + "id": 9, + "name": "Lesley", + "age": 26, + "active": "no" + }, + { + "id": 10, + "name": "Ian", + "age": 20, + "active": "yes" + } + ] |
Conclusion
We hoped you enjoyed this demonstration on how to import a json file into PostgreSQL. We hope this simple demo clears up your understanding of the process. We have lots of tutorials in PostgreSQL so please check our knowledge-base if you have other nagging PostgreSQL questions.
Pilot the ObjectRocket Platform Free!
Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.
Get Started