# JSON Functions and Operators in PostgreSQL

> JSON is the data type in the PostgreSQL database and it can be used to store data in a human-readable textual form which is different from other formats.

In PostgreSQL, data is stored in the tables using columns and rows and each column has a specific name that stores a specific type of data. JSON is a type of data in PostgreSQL that is more useful and efficient in storing data in the PostgreSQL database. JSON keeps the data as key-value pairs. To query JSON data various operators and functions are utilized in Postgres.

This guide will explain the process of using JSON functions and operators in PostgreSQL.

 **JSON Functions and Operators in PostgreSQL**

Postgres support various built-in functions and operators to fetch data using queries from JSON columns. This post will demonstrate the following concepts regarding JSON functions and Operators:

\- Use JSON Data Type in Table  
\- How to Use JSON Operators in Postgres?  
\- How to Use JSON Functions in Postgres?

 **Use JSON Data Type in Table**

Use the following command to create a PostgreSQL table to use JSON data type with its field:
    
    
    CREATE TABLE product (
      prod_id serial NOT NULL PRIMARY KEY,
      description json
       );

The above code creates a table named “ **product** ” and it contains a couple of fields which are “ **prod_id** ” and “ **description** ”. The **prod_id** column is a **primary key** of the table and its data type is **serial** which means that its values will be auto-incremented in a specific order. The **description** field has the data type **JSON** , which means users can apply JSON **operations** and **functions** on it:

Use the following command to insert values in the product table:
    
    
    INSERT INTO product (description)
     VALUES('{ "Laptop": "Lenovo", "Specifications": {"RAM": "10GB","color": "Black"}}'),
     ('{ "Laptop": "Lily Bush", "Specifications": {"RAM": "8GB","color": "Silver"}}'),
     ('{ "Laptop": "Josh William", "Specifications": {"RAM": "6GB","color": "Black"}}'),
      ('{ "Mouse": "Mary Clark", "Specifications": {"Wires/Wireless": "Wireless","color":   "Black"}}');

The above code inserts values in the **description** field which has the **JSON** data type so it contains the **description** of the product such as the **name** of the product and its **specifications** :

Use the following command to fetch the data stored in the description field of the product table:
    
    
    SELECT description
    FROM product;

The above code displays the data stored in the description field with JSON data type:

 **How to Use JSON Operators in Postgres?**

Once the data is stored in the table, use the following code to use JSON operators which in this case is the “ **- >**” operator:
    
    
    SELECT description -> 'Specifications' 
    AS Specifications
    FROM product;

The above code uses the “ **- >**” operator to select a specific key named “Specifications” of the “Description” column:

The following code uses two JSON operators which are “ **- >**” and “ **- >>**” to get the data from the JSON field:
    
    
    SELECT description ->> 'Laptop' AS Laptop
    FROM product
    WHERE description -> 'Specifications' ->> 'RAM' = '8GB';

The above code fetches the data from the **description** field which is stored as the **Laptop** where the **RAM** size is **8GB.** The “ **- >**” operator is used to get the JSON data by key and the “ **- >>**” operator fetches the data as text:

 **How to Use JSON Functions in Postgres?**

After using the JSON operators, the following query uses JSON functions to get data from the table:
    
    
    SELECT json_typeof(description -> 'Specifications')
    FROM product;

The above code uses the **json_typeof()** function which is used to return the **type** of the JSON value which in this case is the “ **object** ” data type:

Execute the following query to use another JSON function which is the **json_each()** function in PostgreSQL:
    
    
    SELECT json_each (description)
    FROM product;

The above code contains the **json_each()** function which enables the user to expand the JSON objects into multiple rows. The above functions separate the JSON field and divide the Laptop and specification section into multiple rows:

Run the following query to use the json_object_keys() function in PostgreSQL:
    
    
    SELECT json_object_keys (description->'Specifications')
    FROM product;

The json_object_keys() function allows the user to get the set of keys from the “specifications” object of the description field:

That’s all about JSON functions and operators in PostgreSQL.

 **Conclusion**

JSON is a data type to store data in the PostgreSQL database that is used when the user has to keep it in the textual format. PostgreSQL provides multiple JSON operators which can be used with SQL queries to fetch data from the JSON column in the table. It also provides different JSON functions to perform specific operations on the JSON field. This guide has explained the process of using JSON functions and operators to query the data stored in the JSON field.

---
[View this page online](https://www.commandprompt.com/education/json-functions-and-operators-in-postgresql/)