PostgreSQL json_each() Function

Postgres tables store data in the form of rows and columns, the same way the JSON data type can store data in the form of key-value pairs. The JSON data types store the JavaScript Object Notation data in PostgreSQL. The key-value pair is a pair of the identifier and its value or values. Postgres offers many functions that can be used to manipulate or interrogate some information about these JSON data types. In this article, we’ll investigate the basic workings of the json_each() function, so let’s get started!

PostgreSQL json_each() Function

We know that a JSON data type stores data in the form of key-value pairs. We can get the top-level JSON objects into a set of expanded key-value pairs. The json_ each() function can be utilized for this specific purpose. The structure of the json_each() function can be written as:

json_each ( obj JSON ) → set_of RECORD( key TEXT, value JSON )

In the above syntax:

● The Json_each() function takes in the JSON object as an argument.

● The Json_each() function returns the set of expanded key-value pairs for the specified JSON object.

● The data type of the “key” is TEXT and for “value” it is JSON data type.

● The two separate columns for “key” and “value” are returned as an output containing all the expanded key-value pairs in the respective top-level JSON object.

Let’s write some queries for the json_each() function to understand the concept in a better way.

Example 1: Understanding the json_each() Function

Let’s assume the following query to understand the concept. The following query contains the data of the record of a student.

SELECT json_each(
  '{"st_name": "Smith", 
  "id": 56,
  "Courses": ["English", "Physics", "Chemistry"]}');

We have provided the top-level JSON object to the json_each() function. The function will now return the data in key-value pairs like this:

We can observe that the query has given the expanded JSON key-value pairs in return.

We can also get the two different columns for the key and the value.

Example 2: Using the json_each() Function to Get Different Columns For Key And Value

In this section, let’s write the query to get the two separate columns for the key and values of the above-considered example. The query can be written as:

SELECT * FROM json_each(
  '{"st_name": "Smith", 
  "id": 56,
  "Courses": ["English", "Physics", "Chemistry"]}');

The output of this query is illustrated below:

The above image illustrates that we were successful in getting two separate columns for the key and values. We can also observe that the data type of the key is TEXT and the value is JSON.

If we want to get the data type of both the columns as TEXT, we need to use the json_each_text() function instead.

Example 3: Using the json_each() Function On Table Data

We can use the json_each() function with the table data as well. We can implement it on the table having a JSON column that contains the JSON objects. To see how to create a table containing a column of JSON data type you can follow the link: Working With PostgreSQL JSON Data.

Here I am considering the table “online_store” which looks like this:

The column “products_in_cart” is of JSON data type and contains the JSON objects. Let’s implement the json_each() function to get the keys and values in the table.

SELECT json_each(products_in_cart) 
 FROM online_store;

Using the above query, we will be able to get the JSON objects from the “product_in_cart” column.

In this way, we can get the key and values from any table using the json_each() function. You can also notice that the RECORD is the data type of the column that is given by the query.

To get the keys and values separately, we’ll write the following query:

SELECT * FROM online_store, json_each(products_in_cart);

The query will return the keys and values in two different columns like this:

We can also note that the return type of the “key” column is TEXT and the “value” column is JSON. so this is how we work with the json_each() function on any Table.

Conclusion

The json_each() function converts the top-level JSON object into the expanded key-value pairs. The JSON object is provided to the json_each() function as an input as it will convert and return the expanded key-value pairs. We understood the basic concept and working of the json_each() function with the help of examples.

PostgreSQL json_each() Function