PostgreSQL – JSON Data Type

JSON stands for JavaScript Object Notation. It is used to store data in the form of key-value pairs and is generally used for communicating between the server and the client. Contrary to other formats, JSON is human-readable text.
PostgreSQL has support for native JSON data type since version 9.2. It offers numerous functions and operators for handling JSON data.

Syntax: variable_name json;

Now let’s look into a few examples for demonstration.

Example 1:
First, create a table (say, orders) using the below command:

CREATE TABLE orders (
    ID serial NOT NULL PRIMARY KEY,
    info json NOT NULL
);

Now insert some data into the orders table as follows:

INSERT INTO orders (info)
VALUES
    (
        '{ "customer": "Raju Kumar", "items": {"product": "coffee", "qty": 6}}'
    );

Now we will query for the orders information using the below command:

SELECT
    info
FROM
    orders;

Output:

Example 2:
In the above example we created an orders table and added single JSON data into it. In this example we will be looking onto inserting multiple JSON data in the same table using the command below:

INSERT INTO orders (info)
VALUES
    (
        '{ "customer": "Nikhil Aggarwal", "items": {"product": "Diaper", "qty": 24}}'
    ),
    (
        '{ "customer": "Anshul Aggarwal", "items": {"product": "Tampons", "qty": 1}}'
    ),
    (
        '{ "customer": "Naveen Arora", "items": {"product": "Toy Train", "qty": 2}}'
    );

Now we will query for the orders information using the below command:

SELECT
    info
FROM
    orders;

Output: