How to Query Postgres Insert into a JSON Column

Have a Database Problem? Speak with an Expert for Free
Get Started >>

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:

1
sudo -u postgres psql

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:

1
CREATE DATABASE dbname;

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:

1
2
3
4
5
CREATE TABLE sample(
        id SERIAL PRIMARY KEY,
        name VARCHAR(100) NOT NULL,
        age INT NOT NULL,
        more_info JSON);

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

1
["id", "name", "age", "more_info"];

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

Use the following syntax to execute the INSERT statement:

1
INSERT INTO TABLE_NAME(col1, col2, col3) VALUES(val1, val2, val3);

The results from the above example should produce the following:

1
2
3
4
INSERT INTO sample(name, age, more_info)
VALUES('Thomas', 22, '{"email": "thom22@gmail.com", "country": "US"}'),
      ('Jerry', 24, '{"email": "jerrybac@gmail.com", "country": "UK"}'),
      ('Annie', 21, '{"email": "anniehan@ymail.com", "country": "AUS"}');

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

1
2
3
4
5
 id |  name  | age |                     more_info                    
----+--------+-----+---------------------------------------------------
  1 | Thomas |  22 | {"email": "thom22@gmail.com", "country": "US"}
  2 | Jerry  |  24 | {"email": "jerrybac@gmail.com", "country": "UK"}
  3 | Annie  |  21 | {"email": "anniehan@ymail.com", "country": "AUS"}

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:

1
2
3
4
5
6
7
8
9
10
11
INSERT INTO sample SELECT id, name, age, more_info FROM
json_populate_record (NULL::sample,
    '{
      "id": "4",
      "name": "Hansel",
      "age": 25,
      "more_info": {
      "email": "hanselcrak54@gmail.com", "country": "PHIL"
      }
    }'

);

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

1
2
3
4
5
6
7
8
 id |  name  | age |                        more_info                        
----+--------+-----+---------------------------------------------------------
  1 | Thomas |  22 | {"email": "thom22@gmail.com", "country": "US"}
  2 | Jerry  |  24 | {"email": "jerrybac@gmail.com", "country": "UK"}
  3 | Annie  |  21 | {"email": "anniehan@ymail.com", "country": "AUS"}
  4 | Hansel |  25 | {                                                      +
    |        |     |    "email": "hanselcrak54@gmail.com", "country": "PHIL"+
    |        |     |    }

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.

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.