How to Import a Json File into PostgreSQL Database Cluster

Written by Data Pilot
March 29, 2020
  • PostgreSQL
Have a Database Problem? Speak with an Expert for Free
Get Started >>

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.

Rate 1 Star2 Stars3 Stars4 Stars5 Stars

Pilot the ObjectRocket Platform Free!

Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.

Get Started
Related Topics:
Additional Information:

Keep in the know!

Subscribe to our emails and we’ll let you know what’s going on at ObjectRocket. We hate spam and make it easy to unsubscribe.