Open In App

PostgreSQL – JSON Data Type

Improve
Improve
Like Article
Like
Save
Share
Report

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:


Last Updated : 22 Feb, 2021
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads