Postgres Insert JSON Example

Introduction

This tutorial will explain how to use Postgres to insert JSON data using SQL statements in psql. Psql is a front-end to PostgreSQL that allows for inserting record data into a PostgreSQL table in the form of a JSON object using the psql command-line interface. This allows for issuing interactive queries to PostgreSQL and then immediately viewing the results. Inputs can also be made from command-line arguments or from an existing file.

Prerequisites

  • Postgres and the command-line interface psql must be installed and working. Ideally, Postgres version 9 or greater should be used to with Postgres to insert JSON data using SQL statements in psql. Execute the postgres -V command to determine the currently installed version of Postgres.

  • Some JSON data must be prepared for inserting into a Postgres table. For multiple records, each JSON document must be nested inside of an array. This tutorial will use the following test data in the form of a JSON array:

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": "0",
    "str_col": "orkb type foo examples tutorials orkb",
    "int_col": 5743,
    "bool_col": true,
    "json_col": {
      "ObjectRocket": "examples"
    },
    "float_col": 94.55681687716474
  },
  {
    "id": "1",
    "str_col": "Postgres bar data",
    "int_col": 3151,
    "bool_col": false,
    "json_col": {
      "data": "tutorials"
    },
    "float_col": 11.51099159756918
  },
  {
    "id": "2",
    "str_col": "Postgres tutorials data data",
    "int_col": 4237,
    "bool_col": true,
    "json_col": {
        "type": "type"
    },
    "float_col": 48.94065780742467
  }
]

Access psql

Execute the psql command to enter into the psql command-line interface.

Now connect to a database, that will store the JSON data as a PostgreSQL table, using the following \c command:

1
\c json_db

A Postgres user and database can also be accessed by using the -U and -d flags, respectively, while the psql command is executed in a terminal. Here is an example:

1
psql -U orkb -d json_db

PostgreSQL ‘CREATE TABLE’

A table must first be created for the JSON data before an INSERT statement can be performed. However, the table’s columns must conform to the keys of the JSON data.

Assume the JSON data uses the following keys in a JavaScript array:

1
["id", "str_col", "int_col", "bool_col", "json_col", "float_col"];

In this case, the CREATE TABLE SQL statement should resemble the following:

1
2
3
4
5
6
7
8
CREATE TABLE json_table (
    id VARCHAR(50) PRIMARY KEY,
    str_col VARCHAR(500),
    int_col SMALLINT,
    bool_col BOOLEAN,
    json_col JSON,
    float_col DECIMAL
);

Note that the above command should return a response of CREATE TABLE.

Using psql to insert JSON

JSON data can now be inserted into the new Postgres table. Execute the Postgres json_populate_record() function to populate an object with JSON data before inserting it into the table.

Insert a JSON document into Postgres

The following example shows how to insert JSON data as Postgres records into a NULL object so that the data can be inserted into a table:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
INSERT INTO json_table
SELECT id, str_col, int_col, bool_col, json_col, float_col
FROM json_populate_record (NULL::json_table,
    '{
      "id": "0",
      "str_col": "orkb type foo examples tutorials orkb",
      "int_col": 5743,
      "bool_col": true,
      "json_col": {
        "ObjectRocket": "examples"
      },
      "float_col": 94.55681687716474
    }'

);

NOTE: The double colons (::) in the above SQL statement denote a type of data conversion. In this case it converts a NULL object into data that can be digested by the Postgres table using the json_populate_record() function. An example is provided in the following screenshot:

Screenshot of Postgres INSERT JSON data in psql using json_populate_record When the above SQL statement is executed, a single record should be inherited into the Postgres table. Use the SELECT keyword to retrieve the data, as shown in the following example:

1
SELECT * FROM json_table;

The above statement should return a response that resembles the following table:

1
2
3
4
5
6
 id |                str_col                | int_col | bool_col |              json_col              |     float_col
----+---------------------------------------+---------+----------+------------------------------------+-------------------
 0  | orkb TYPE foo examples tutorials orkb |    5743 | t        | {                                 +| 94.55681687716474
    |                                       |         |          |         "ObjectRocket": "examples"+|
    |                                       |         |          |       }                            |
(1 ROW)

Postgres insert multiple rows of JSON

This section will explain how to insert multiple JSON records using the json_populate_recordset() SQL function. The following example shows how to insert two additional JSON records into the Postgres 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
INSERT INTO json_table
SELECT * FROM json_populate_recordset (NULL::json_table,
  '[
    {
      "id": "1",
      "str_col": "Postgres bar data",
      "int_col": 3151,
      "bool_col": false,
      "json_col": {
        "data": "tutorials"
      },
      "float_col": 11.51099159756918
    },
    {
      "id": "2",
      "str_col": "Postgres tutorials data data",
      "int_col": 4237,
      "bool_col": true,
      "json_col": {
          "type": "type"
      },
      "float_col": 48.94065780742467
    }
  ]'
);

NOTE: An ERROR: cannot call populate_composite on an array error message will be displayed if the above SQL statement is attempted using the json_populate_record() function explained previously.

However, executing the SELECT id FROM json_table; statement should return three IDs in a table that resemble the following data:

1
2
3
4
5
6
 id
----
 0
 1
 2
(3 ROWS)

The results are displayed in the following screenshot:

Postgres INSERT JSON multiple records into psql using json_populate_recordset

When finished, execute the \q command to quit or exit the psql command-line interface.

Conclusion

This tutorial explained how to use Postgres to insert JSON data into a PostgreSQL table using SQL statements in psql. The tutorial specifically explained how to access psql and execute the psql command to enter into the psql command-line interface. The tutorial also explained how to connect to the database that will store the JSON data as a PostgreSQL table and how to execute the PostgreSQL ‘CREATE TABLE’ command. The article also covered how to use psql to insert a JSON document into Postgres as well as how to insert multiple rows of JSON data into a table. Remember that an ERROR: cannot call populate_composite on an array error message will be displayed if a SQL statement is attempted using the json_populate_record() function when inserting JSON data into a PostgreSQL table. Here the SELECT id FROM json_table; statement must be used instead.

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.