PostgreSQL JSONIn 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 typeThe Syntax for PostgreSQL JSON data type is as follows: Example of PostgreSQL JSON data typeLet 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. 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. 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: 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 dataAfter creating and inserting the Purchase table's values, we will use the SELECT command to retrieve the JSON data of the Purchase table: 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 DataTo 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: 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: 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: 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 functionsIn 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. 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 clauseTo 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: 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: 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 functionsWe 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 functionWe 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. 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 functionIf 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: 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: Output On implementing the above command, we will get a similar output compared to the above json_each() function. json_typeof functionTo 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: 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. Output After executing the above command, we will get the following output, which displays the nested item JSON object's qty field data type. OverviewIn the PostgreSQL JSON data type section, we have learned the following topics:
Next TopicPostgreSQL hstore |