How to Import a Json File into PostgreSQL Database Cluster

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.

Pilot the ObjectRocket Platform Free!

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

Get Started

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.