JSON

In this section, we are going to understand the working of the PostgreSQL JSON data type, examples of the JSON data type, and some accessible functions json_each(), json_object_keys (), json_typeof(), etc.

We also see JSON operator’s example with WHERE clause, which helps us to handle JSON data values more resourcefully, and we will use some aggregate function (SUM, MIN, AVG, MAX) to get the JSON data.

What is PostgreSQL JSON Data Type?

Another data type in PostgreSQL is JSON, which stands for JavaScript Object Notation. It is an open-standard format that contains key-value pairs.

The main objective of using the JSON data type is to transfer data between a server and a web application. JSON is human-readable text distinct from the other formats.

Since the 9.2 version of PostgreSQL supports the JSON data type, which contains several operators and functions for operating the JSON data values.

Syntax of PostgreSQL JSON data type

The Syntax for PostgreSQL JSON data type is as follows:

variable_name JSON  

Example of PostgreSQL JSON data type

Let us see one sample examples to understand how the PostgreSQL JSON data type works.

We are creating one new table as Purchase with the CREATE command’s help and inserting some values using the INSERT command.

To create a Purchase into an Organization database, we use the CREATE command.

The Purchase table contains the two columns, such as Purchase_id and Puchase_description.

Here, the Purchase_id column is the primary key column, which categorizes the purchase, and for the Puchase_description column, we use the JSON data type, which stores the data in the form of JSON.

 CREATE TABLE Purchase (  

    Purchase_id serial NOT NULL PRIMARY KEY,  

    Purchase_description json NOT NULL  

); 

Output

We will get the following message on executing the above command, which displays that the Purchase table has been created successfully into the Organization database.

After creating the Purchase table successfully, we will insert values into a JSON column with the INSERT command’s help. And we also make sure that the data is in a valid JSON format.

The below INSERT command is used to insert a new row into the Purchase table.

  INSERT INTO Purchase (Purchase_description)  

VALUES('{ "purchaser": "Olivia Smith", "items": {"product": "iphone11 pro max","qty": 1}}');  

Output

After implementing the above command, we will get the following message window, which displays that the values have been inserted successfully into the Purchase table.

The above command implies that Olivia Smith bought one iPhone 11 pro max.

Now, we will insert various rows into the Purchase table with the help of the below command:

INSERT INTO Purchase (Purchase_description)  

VALUES('{ "purchaser": "Maria Rodriguez", "items": {"product": "Hair shampoo","qty": 2}}'),  

('{ "purchaser": "Thomas Jones", "items": {"product": "Belgium chocolate ice cream","qty": 7}}'),  

('{ "purchaser": "Margaret Davis", "items": {"product": "Barbie doll","qty": 3}}'),  

 ('{ "purchaser": "Elizabeth Brown", "items": {"product": "puzzle set","qty": 10}}'); 

Output

After implementing the above command, we will get the following message window, which displays that the multiple values have been inserted successfully into the Purchase table.

Retrieving the JSON data

After creating and inserting the Purchase table’s values, we will use the SELECT command to retrieve the JSON data of the Purchase table:

SELECT Puchase_description   

FROM Purchase; 

Output

After successfully implementing the above command, we will get the below result, which displays that the PostgreSQL returns the output in the form of JSON present in the Purchase table:

PostgreSQL operators to get the JSON Data

To get the JSON data, PostgreSQL allows us two native operators, which are as follows

• ->
• ->>

Here, the -> Operator is used to retrieve the JSON object field by key, and the ->> Operator is used to retrieve the JSON object field by text.

To get all Purchaser in the form of JSON, we will use -> Operator in the below command:

  SELECT Purchase_description  -> 'purchaser' AS pruchaser  

FROM Purchase;  

Output

After implementing the above command, we will get the below result, which displays that all the Purchaser in the form of JSON with the help of -> Operator, as shown below:

To retrieve all the purchaser in the form of text, we will use the ->> operator, as we can see in the below command:

SELECT Purchase_description  ->> 'purchaser' AS pruchaser  

FROM Purchase;

Output

After successfully executing the above command, we will get all Purchaser in the form of text with the help of ->> operator in the output, as shown below:

As we understood above, the -> Operator retrieves a JSON object to combine it with the ->> Operator to return a particular node.

Let us see one sample example for our better understanding:

To get all products sold, we will use the below command:

  SELECT Purchase_description -> 'items' ->> 'product' as Product  

FROM Purchase  

ORDER BY Product;  

Output

We will get the following output on implementing the above command where the first Purchase_description -> ‘items’ will retrieve items as per the JSON objects.

And second Purchase_description ->’items’->>’product’ statement will retrieve all products in the form of text.

To get JSON data using aggregate functions

In PostgreSQL, we have the following aggregate functions like MAX, MIN, AVERAGE, SUM, etc. which we are going to retrieve the JSON data.

Let us see on sample example as shown below:

In the below command, we will try to get the maximum, minimum, average, and the total quantities of products purchased in the Purchase table.

SELECT   

   MAX (CAST (Purchase_description-> 'items' ->> 'qty' AS INTEGER)),  

   MIN (CAST (Purchase_description -> 'items' ->> 'qty' AS INTEGER)),  

   AVG (CAST (Purchase_description -> 'items' ->> 'qty' AS INTEGER)),  

   SUM (CAST (Purchase_description -> 'items' ->> 'qty' AS INTEGER))  

FROM Purchase; 

Output

After successfully implementing the above command, we will get the below output, which displays all the purchases products maximum, minimum, average, and total quantities in the Purchase table:

Using JSON operator in WHERE clause

To filter the retrieving rows, we will use the JSON operators in the WHERE clause.

In the below example, we will identify who bought the Belgium chocolate ice cream with the help of the below command:

 SELECT Purchase_description ->> 'purchaser' AS Purchaser  

FROM Purchase  

WHERE Purchase_description-> 'items' ->> 'product' = 'Belgium chocolate ice cream'; 

Output

We will get the below result after executing the above command, which displays that Thomas Jones purchased the Belgium chocolate ice cream from the Purchase table.

In the following example, we will identify who bought three products at a time using the below command:

SELECT Purchase_description  ->> 'purchaser' AS Purchaser,  

Purchase_description -> 'items' ->> 'product' AS product  

FROM Purchase  

WHERE CAST ( Purchase_description-> 'items' ->> 'qty' AS INTEGER) = 3

Output

On implementing the above command, we will get the below result, which displays that Margaret Davis purchased three products from the Purchase table.

Note: In the above command, we have used the typecast to modify the qty field into INTEGER type and relate it with two.

PostgreSQL JSON functions

We have the following JSON functions such as json_each(), json_object_keys (), json_typeof(), etc., available in the PostgreSQL, which help us to enhance the performance while we are using the JSON data type.

Let us see them one by one to understand how the PostgreSQL JSON functions work.

For this, we are taking the above Purchase table, which we created earlier in this tutorial, into an Organization database using the CREATE command.

json_object_keys function

We can use the json_object_keys() function to retrieve a set of keys in the outermost JSON object.

For example: In the below command, we use the json_object_keys() function to get all the keys of the nested items object in the Purchase_description column from the Purchase table.

SELECT json_object_keys (Purchase_description->'items')  

FROM Purchase; 

Output

After executing the above command, we will get the below output, which displays all keys of the nested items object with the help of json_object_keys() function.

json_each function

If we want to increase the outermost JSON object into a set of key-value pairs, we can use the json_each() function.

Let see one sample example to understand in detail:

In the below example, we will try to retrieve the outermost JSON object into a set of key-value pairs in the Purchase_description column from the Purchase table, as shown in the following command:

SELECT json_each (Purchase_description)  

FROM Purchase;

Output

After successfully executing the above command, we will get the below output, which displays all the outermost JSON object into a set of key-value pairs in the Purchase_description column from the Purchase table.

We can also use the json_each_text() function in its place of json_each() function, if we need to retrieve a set of key-value pairs as text.

For example

In the following command, we will use the json_each_text() function instead of json_each() function:

SELECT json_each_text(Purchase_description)  

FROM Purchase;

Output

On implementing the above command, we will get a similar output compared to the above json_each() function.

json_typeof function

To retrieve the type of outermost JSON value as a string, we can use the json_typeof() function. And the json_typeof() can accept the Boolean, number, object, null, string, and array data values.

For example: In the below command, we will try to get the data type of the items present in the Purchase table:

SELECT json_typeof(Purchase_description->'items')  

FROM Purchase; 

Output

After executing the above command, we will get the following result, which displays the outermost json value as a string:

The below command is used to retrieve the qty field data type of the nested items JSON object.

 SELECT json_typeof(Purchase_description->'items'->'qty')  

FROM Purchase; 

Output

After executing the above command, we will get the following output, which displays the nested item JSON object’s qty field data type.

Overview

In the PostgreSQL JSON data type section, we have learned the following topics:

• The PostgreSQL JSON data type is used to store the JSON values for a specified column.
• We have used the SON Operator within the WHERE clause for filtering the retrieving rows from the specified table.
• We also used the aggregate functions such as Average, Sum, Min, Max to get the JSON data.
• We used the different JSON functions, for example, json_each(), json_object_keys (), json_typeof(), etc.,to enhance and handle the JSON value from the particular table.
• We used the JSON operators to get the JSON Data more successfully for the table’s particular column.