# PostgreSQL json_each() Function

> The json_each() function converts the top-level JSON object into the expanded key-value pairs. It accepts a JSON object as an input and converts and returns th…

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](<https://www.commandprompt.com/education/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.

---
[View this page online](https://www.commandprompt.com/education/postgresql-json_each-function/)