# JSONB Data Type in PostgreSQL

> JSONB is the binary format of storing and exchanging data in JSON syntax which is a human-readable format and PostgreSQL allows the use of JSONB data types.

JSON or JavaScript Object Notation is a lightweight format for storing and transporting data in the form of text. It is used when the data is sent from the server to a particular web page and the user can extract data from the text using JSON syntax. JSON text is human-readable and PostgreSQL supports the JSON data types while creating tables or updating columns in the PostgreSQL table.

This guide will explain JSONB data types in the PostgreSQL database.

 **JSONB Data Type in PostgreSQL**

It was becoming difficult for PostgreSQL to process and analyze the huge amount of textual form of data so they opted for the binary form of JSON. JSONB in PostgreSQL is a data type that can be used to store data in decomposed binary format. It also enhances/improves performance significantly as it simplifies schema designs and provides efficiency by creating indexes on JSONB columns.

 **Example 1: How to Create a Table With JSONB Data Type**

Use the following query to create a table that uses JSONB data type for one of its columns:
    
    
    CREATE TABLE emp (
     emp_id int NOT NULL,
     data jsonb
     );

The above code creates a PostgreSQL table called “ **emp** ” and it contains two columns that are **emp_id** and **data**. The **emp_id** column has the **int** data type and it has the constraint of not having a NULL value at any time. The **data** field has the datatype of the **jsonb** so it can contain multiple values or a list in the field:

Once the table is created successfully, the user needs to insert values in it using the following query:
    
    
    INSERT INTO emp VALUES 
     (1, '{"name": "John", "hobbies": ["Movies", "Football", "Hiking"]}'), 
     (2, '{"name": "Marc", "hobbies": ["Gaming", "Movies", "Music"]}');

The above code inserts two rows containing the information regarding employees’ **ids** , **names,** and **hobbies** :

Type the following query to get all the data stored in the emp table:
    
    
    SELECT * FROM emp;

Running the above code displays the data stored in the **emp** table containing the JSONB datatype:

 **Example 2: How to Fetch the Specific Key of a JSONB Object**

The " **- >**" operator in Postgres retrieves the value of a specific key/index from a JSONB object or array stored in a JSONB field. Use the following command containing the **- >** operator to get the name from the data column and display it on the screen:
    
    
    SELECT data->'name' FROM emp;

Running the above code only displays the names of the employees from the JSONB column:

Use the following query to get the list of hobbies from the data column:
    
    
    SELECT data->'hobbies' FROM emp;

Executing the above code has displayed the list of hobbies for both employees:

 **Example 3: How to Fetch Specific Data From a JSONB Column**

Use the following command to get the specific key’s data from the selected column:
    
    
    SELECT data->'hobbies' FROM emp 
     WHERE data->'name' = '"Marc"';

The above code will display only **hobbies** from the data column of the employee “ **Marc** ”:

 **Example 4: Display a List of Hobbies**

Use the following code to get the list of all the hobbies stored in the table:
    
    
    SELECT jsonb_array_elements_text(data->'hobbies') 
     AS hobbies FROM emp;

Running the above query displays the list of all the hobbies in a separate cell:

Use the following query to get the **hobbies** but with their representation according to the **emp_id** column:
    
    
    SELECT emp_id, jsonb_array_elements_text(data->'hobbies') 
    AS hobbies FROM emp;

The above code displays the list of all the hobbies with reference to the emp_id column as each hobby belongs to a particular employee:

 **Example 5: Use @ Operator With WHERE Clause**

Use the “ **@** ” operator to get data from the employee table with reference to a particular hobby:
    
    
    SELECT emp_id, data FROM emp 
     WHERE data->'hobbies' @> '["Gaming"]'::jsonb;

The above query only displays the data of the employee whose hobby list contains “ **Gaming** ” data:

Use the following query to get the list of employees who has the hobby of watching “ **Movies** ”:
    
    
    SELECT emp_id, data FROM emp 
    WHERE data->'hobbies' @> '["Movies"]'::jsonb;

The above query displays that both employees have the hobby “ **Movies** ” in their data list:

That’s all about the JSONB data type in the PostgreSQL table.

 **Conclusion**

JSONB data type is used to create columns in the PostgreSQL table like other types such as integer, character, etc. A JSONB data type column can store a list of data in a single field like the example from this guide displays the name and hobbies in the same column. The user can get data from the JSONB column using different operators like “ **- >**” and “ **@** ”. This guide has explained the JSONB data type in PostgreSQL and different examples to use it.

---
[View this page online](https://www.commandprompt.com/education/jsonb-data-type-in-postgresql/)