PostgreSQL JSON Functions

JSON is an open-style JavaScript Object Notation used only for pairs of key-value data within the PostgreSQL database. JSON consists of many operators and functions to query the JSON information or data. In this article, we will demonstrate the working of JSON functions on some JSON data within the PostgreSQL tool. To work on JSON functions, one must have some JSON data in the database. Make sure you have PostgreSQL pgAmdin 4 installed and configured on your Windows 10 system because we have been implementing this article at Windows 10 on the PostgreSQL pgAdmin 4. Open your PostgreSQL Graphical user interface, e.g., pgAdmin, from the start bar of Windows 10 by searching it thoroughly. After that, it may require your master password and server password to get into it one after another. After adding the password, you have to tap on any database listed within the list of Servers and open the query tool to add some commands.

Create JSON Column/Table:

Now the query tool has been opened, you have to add some code to it to create JSON data. We have used the CREATE TABLE command to create a new table “Makeup” within the built-in database Postgres. This table contains two columns. One of them stores the ID of the makeup product, and the system automatically increments it. Another is JSON type column “data” used to store the key-value pair information within this column of the Makeup table. Another command, e.g., INSERT INT, has been used to insert the key-value pairs or JSON type information regarding makeup products into a column “data” of table Makeup. The “run” button on the taskbar of query editor has been pressed to execute this code, as shown below. The queries worked perfectly fine, and the success message has been shown in the Output area.

To see if the JSON type column and data have been created and inserted within the Postgres database successfully, you must see the Tables area. There you will find a table “Makeup”. Right-click on it and hover over the option of “View/Edit Data”. It will expand further, and you have to tap the “All Rows” option to fetch the data in a grid view. The output will be as same as demonstrated in the picture below. You can see the JSON data in the table.

Json_each:

Let’s explore some JSON functions to fetch the key-value pairs of the respective column “data” of the table “Makeup”. Our first JSON method will be the json_each() method to expand most outer objects of JSON into a pair of key-value. In the above-shown table, “Product” is an outermost key and the “Highlighter” is the outermost value of it. The other values in the brackets will be the value of a key outside of the bracket, e.g., “details”. The json_Each function takes the column “data” as a parameter within the SELECT query to fetch the key-value outermost pair. The query stated below has given us the 10 pairs of key-value for 5 records of the table “Makeup” from the column “data”.

# SELECT json_each (column_name) FROM TABLE_NAME;

Json_each_text:

This method of JSON works the same as the above function, but it returns the value of a key as “text” type within the output. That’s why we have not been using the “Makeup” table here. So, we have used the below query having json_each_text function using key-value pairs within it. The output shows the keys and values separately as text types within the output table.

# SELECT * FROM json_each_text(‘{“KEY”: “VALUE”, “KEY”: “VALUE”}’);

Json_object_keys:

This function of JSON has been used to fetch the keys of the most outer object of JSON data. To access keys from the data, you have to assign the outermost object of JSON data as a parameter to this function. Let suppose we have been giving the column “data” within the parameter of json_object_keys(), while indicating the object “details” with an operator. This means it will go to the column “data”, and within this column, it will fetch the keys of an object “details” from the table Makeup. Upon using this function within the SELECT query of PostgreSQL, we have got the keys of an object “details” e.g., shades and total, as per the output. Don’t forget to use the “Run” button from the taskbar of the query editor to run it. There are a total of 10 keys found from the object “details” of column “data”. The query is stated as follows:

# SELECT json_object_keys (column_name-> ‘object_name) FROM TABLE_NAME;

Json_typeof:

This function of JSON is specifically used within the SELECT command to tell us the type of data of any object. For this purpose, we have used the column “data” and mentioned the object “details” to see what type of data the key “details” have. We have used the stated below command in the editor followed by the “Run” button. As the “details” is already an outermost object, it shows the type as “object” as per the output table.

# SELECT json_typeof (column_name -> ‘object’) FROM TABLE_NAME;

You can also see the type of data that any object has by getting more into it. For this purpose, you have to mention that key within the parameter of a function to get its value type. So, we have mentioned the column “data”, and within it, we will be checking the value type of a key “total” of an outermost object “detail”. The query below has been executed via the “Run” button in the query editor and got the results as shown in the image. It shows that the value of a key “total” is of “number” type. This means that the value must be some integer, and that’s true.

# SELECT json_typeof (column_name ‘object’ -> ‘KEY’) FROM TABLE_NAME;

Array_to_Json:

This method has been used in PostgreSQL to convert an array into a JSON array of arrays. For this purpose, we will assign an array to this function and run it as per the stated command below. You can see that we have mentioned a multidimensional array within this function as a parametric value. The function has converted its array to a JSON type row as per the output shown in the picture. Don’t forget to mention the type of array within the parameter.

# SELECT array_to_json(‘{{1,6},{12.35}}’::INT[]);

Jsonb_pretty:

This method is a bonus for this article. This method has been part of JSONB data types. This method has been used to convert your data into a pretty style for more human consumption. It makes your data more readable and easy for users to understand. So, we have applied this function of some “key-value” pairs of JSOBb data types and pressed the “Run” button. Double-tap on the output value row to see the results.

This function has converted the data into a functional format as per the output.

Conclusion:

This article consists of the implementation of some most popular JSON functions within the PostgreSQL including: json_each, json_each_text, json_typeof, array_to_json and json_object_keys. To better understand JSON functions, you must at least try each function to as many example codes as possible. We hope this article guide will help you at its best, and you will get better at JSON functions.