PostgreSQL is a famously used relational database that stores and manipulates data securely, effectively, and effortlessly. It uses different data types to store the data, such as INT, CHAR, TEXT, etc. Other than these commonly used data types, it offers some special types to achieve specific tasks. One such data type is JSON which stores the data in the form of key-value pairs. The term “JSON” is the acronym for “JavaScript Object Notation”. It is an open standard format for transferring data between a server and web apps. Postgres offers various operators and functions to manipulate the JSON data.

This post will demonstrate how to work with JSON data in PostgreSQL. In this regard, this blog will discuss the following topics:

• How to Define a Table’s Column With JSON
• How to Insert Data into a JSON Field/Column
• How to Query JSON Data in PostgreSQL
• How to Query JSON Data Using JSON Operators
• How to Get a Specific Node From a JSON Object in PostgreSQL
• How to Filter JSON Data in PostgreSQL
• How Do Aggregate Functions Work With the JSON Data?

How to Define a Table’s Column With JSON

To create a table’s column with JSON data type, you can specify the column’s name followed by the “JSON” data type:

col_name JSON;

In the following example, we create a table named “order_details” with two columns: “o_id” and “o_details”:

CREATE TABLE order_details(
o_id SERIAL PRIMARY KEY,
o_details JSON
);

The “o_details” column will accept JSON data:

The table named “order_details” with a JSON column has been created.

How to Insert Data into a JSON Field/Column

The “INSERT INTO” statement is used in Postgres to insert JSON data into a specific table’s column. However, make sure that the data to be inserted is in a valid JSON format (i.e., key-value pairs):

INSERT INTO order_details(o_details)
VALUES
('{ "cust_name": "Joseph", "pro_description": {"pro_name": "Laptop","qty": 1}}'),
('{ "cust_name": "Joe", "pro_description": {"pro_name": "Charger","qty": 3}}'),
('{ "cust_name": "Mike", "pro_description": {"pro_name": "Keyboard","qty": 2}}'),
('{ "cust_name": "Stephen", "pro_description": {"pro_name": "Mouse","qty": 4}}'),
('{ "cust_name": "Kane", "pro_description": {"pro_name": "USB Flash Drives","qty": 5}}');

The output snippet verifies that the JSON data has been inserted into the selected Postgres table.

How to Query JSON Data in PostgreSQL

You can use/execute the below-provided query to fetch the data from the “order_details” table:

SELECT * FROM order_details
ORDER BY o_id ASC;

To query only JSON column/data from the selected table, you can use the SELECT query as follows:

SELECT o_details FROM order_details;

The output signifies that the SELECT query retrieves the data from the JSON column.

How to Query/Fetch JSON Data Using JSON Operators

A couple of native operators are used in PostgreSQL to get a JSON object or a specific node. For instance, the short arrow “->” retrieves the JSON object by “key”, while the “->>” operator retrieves the JSON object by “text”.

In the following code snippet, we utilize the “->” operator to get the product description in the form of JSON:

SELECT o_details -> 'cust_name' As Names
FROM order_details;

The output snippet signifies that the “->” operator retrieves the data in JSON format. Replacing the “->” operator with the “->>” operator will retrieve the data in text format:

SELECT o_details ->> 'cust_name' As Names
FROM order_details;

The output snippet shows that the “->>” operator retrieves the data in TEXT format.

How to Get a Specific Node From a JSON Object in PostgreSQL

To get a specific node from a JSON object, you must use the “->” and “->>” operators together. The “->” operator will retrieve a JSON object while the “->>” operator will retrieve a specific node from that object. For instance, we utilize the “->>” operator that will retrieve a JSON object:

SELECT o_details -> 'pro_description' As product_info
FROM order_details;

Now, we will use the “->>” operator with the “->” operator to get only the “pro_name” node from the given JSON object:

SELECT o_details -> 'pro_description' ->> 'pro_name' As product_name
FROM order_details;

This way, you can access a specific node of a JSON object.

How to Filter JSON Data in PostgreSQL

You can use the JSON operators, such as short arrow and long arrow along with the WHERE clause to filter the result set of a query (JSON Data). For instance, in the following example, we will filter the result set to find out the customer names who bought a “laptop” or “USB Flash Drives”:

SELECT o_details -> 'cust_name' As customer_name
FROM order_details
WHERE o_details -> 'pro_description' ->> 'pro_name' = 'Laptop' OR 
o_details -> 'pro_description' ->> 'pro_name' = 'USB Flash Drives';

The output snippet demonstrates that the JSON operators retrieve the filtered data based on the specified condition.

How Do Aggregate Functions Work With the JSON Data?

Postgres allows us to use aggregate functions on the JSON data to achieve different functionalities. Suppose we want to calculate the most sold product, least sold product, average sold product, and total sold products. For this purpose, we will use the aggregate functions, as follows:

SELECT 
MAX (CAST (o_details -> 'pro_description' ->> 'qty' AS INTEGER)) AS most_sold,
MIN (CAST (o_details -> 'pro_description' ->> 'qty' AS INTEGER)) AS least_sold,   
AVG (CAST (o_details -> 'pro_description' ->> 'qty' AS INTEGER)) AS average_sold,
SUM (CAST (o_details -> 'pro_description' ->> 'qty' AS INTEGER)) AS total_sold
FROM order_details;

In the above snippet, the CAST operator is utilized to convert the data type of the “pro_description” column to INTEGER. The aggregate functions MAX(), MIN(), AVG(), and SUM() are used to find the most sold product, least sold product, average sold product, and total sold products, respectively.

This is how the Aggregate functions work with the JSON data.

Important: Go through the linked article to learn more about JSON Functions and Operators.

Conclusion

The JSON data type in PostgreSQL stores the data in the form of key-value pairs. To create a table’s column with JSON data type, you can specify the column’s name followed by the “JSON” data type like this: "col_name JSON". You can use the JSON operators and functions to manipulate the JSON data efficiently.

This blog has covered the basics of the JSON data type, such as how to create a JSON data type, how to insert data into a JSON column, how to query JSON data, etc.