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.