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:
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:
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