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.