How to Query Postgres Insert into a JSON Column

Introduction

Standing for JavaScript Object Notation, JSON is an open-standard language format consisting of key-value pairs that can store and transport data. One of the JSON data types PostgreSQL provides for is stored as text, however, the data must be in a valid data type column JSON format in order to store the values in a PostgreSQL table. This tutorial will provide explanations and examples on how to query Postgres to insert into a JSON column in a PostgreSQL table.

Prerequisites

• PostgreSQL must be properly installed, configured and working on the local device in order to query Postgres to insert into a JSON column in a PostgreSQL table.

Open the psql command line console

Before Postgres can be used to insert data into a JSON column, the psql interactive shell, used to execute the PostgreSQL commands, must first be accessed with the following command:

Now enter the sudo root password to complete access to Postgres.

Create a database in PostgreSQL

A database can be created once psql is accessed in the terminal. If a database is already set up, skip this step and proceed to the next section on creating a table with a JSON column.

Execute the following command to create a database in PostgreSQL:

NOTE: Be sure to provide the newly created database with a meaningful name.

• Use the \l command to search for known databases in Postgres. Then execute the \c command followed by the database name to complete the connection.

Create a table in PostgreSQL

Execute the following syntax to create a table with a JSON column in PostgreSQL:

In the above example, assume the sample table uses a Javascript array for the keys. The results should resemble the following:

Using the insert statement to insert a JSON document into the column

Use the following syntax to execute the INSERT statement:

The results from the above example should produce the following:

Now execute the SELECT * FROM sample command to display the JSON value. This should produce the following table:

Insert into a JSON column using the JSON function in PostgreSQL

Following is an example showing how to insert JSON data into a column with a JSON data type using the json_populate_record command:

Now executing the SELECT statement again should produce the following results:

Note that the above INSERT statement query has a double colon (::) indicating the transition of the data. The NULL will then be transformed into data that can be used for executing the Postgres and json_populate_record function.

Conclusion

This tutorial provided explanations and examples on how to query Postgres to insert into a JSON column in a PostgreSQL table. The tutorial explained how to open the psql command line console, create a database and a table in PostgreSQL. The article then covered how to use the INSERT statement and JSON function in PostgreSQL to insert a JSON document into the column along with working examples of the functions. Remember that the INSERT statement query uses a double colon (::) to indicate the transition of data and the NULL will then be transformed into data for executing the Postgres and json_populate_record function.