What is JSONB in PostgreSQL?

Last Updated : 27 Sep, 2024

PostgreSQL is a powerful object-relational database management system that excels at handling structured and semi-structured data, especially through its support for JSONB. JSONB (Binary JSON) allows efficient storage and querying of JSON data and making it ideal for applications that require quick access to structured information.

In this article, we will explain JSONB in detail, explain how to manipulate it, and explain why JSONB is important in PostgreSQL with the help of practical examples.

Understanding JSON

JSON (JavaScript Object Notation) is a lightweight data-interchange format that is easy for humans to read and write and easy for machines to parse and generate. A basic example of a JSON object is:

Query:

{
  "name": "John",
  "age": 30,
  "city": "New York"
}

JSONB in PostgreSQL

JSONB is a binary representation of JSON data in PostgreSQL, offering several key advantages over the standard JSON type:

Speed: JSONB enables faster querying and data manipulation compared to JSON due to its binary storage format.
Indexing: JSONB supports the creation of indexes, allowing for improved query performance, especially on large datasets.
Efficient Storage: By storing data in a binary format, PostgreSQL can optimize how it organizes and retrieves this data.
Flexible Querying: JSONB supports a variety of operators and functions that facilitate working with semi-structured data.

Creating a Products Table with JSONB

Let's create a database to manage product information using JSONB. You can insert data into the JSONB column using the ::jsonb cast. Here’s how you can insert product information into the products table. The name column stores the value 'Smartphone'.

The details column stores a JSONB object with the product's brand, model, and features.
The ::jsonb operator ensures the JSON string is converted to JSONB format.
The table will store product names and details in a JSONB column.

Query:

CREATE TABLE products (
id SERIAL PRIMARY KEY,
name TEXT,
details JSONB
);

INSERT INTO products (name, details)
VALUES (
'Smartphone',
'{"brand": "TechCorp", "model": "X100", "features": {"camera": "12MP", "battery": "4000mAh"}}'::jsonb
);

Output:

id	name	details
1	Smartphone	{"brand": "TechCorp", "model": "X100", "features": {"camera": "12MP", "battery": "4000mAh"}}

Explanation:

This query yields a table by the name of the products with three columns: id, name, and details.
The details column is of type JSONB, which means it will hold the product attributes in JSON format.
A new row is inserted with the name value 'Smartphone' and details containing the JSON data.
After running this query, PostgreSQL will insert the product information into the table and return INSERT 0 1, confirming that one row has been added.

Overview of JSON Functions and Operators

JSON and JSONB support several functions and operators in PostgreSQL to perform several operations and functions. Some common operators include:

->: Returns the value of one or more fields enclosed in curly braces and separated by commas using a single key to find an object.
->>: Similarly to extracting field by key, to extract an object’s field it takes the form of JSON and returns it as text.
@>: Determines if one JSON object is nested in another JSON object.
#>: Get sub-object from JSON using path.

Retrieving and Querying JSONB Data

You can use the -> and ->> operators to query specific fields in a JSONB column. This query retrieves data from the products table, specifically targeting rows where the brand in the details JSONB field is 'TechCorp'. It extracts and returns the name of the product along with the camera specification found in the features field of the details JSONB column. Here's an example:

Query:

SELECT name, details->'features'->>'camera' AS camera_spec
FROM products
WHERE details->>'brand' = 'TechCorp';

Output:

Explanation:

This query retrieves the name of products where the brand is TechCorp and returns the camera specification from the details JSONB field.

Working with JSON Arrays in PostgreSQL

Arrays are also supported by JSONB, which is a data structure that store multiple data values in an ordered manner. It is also important to learn that we can insert and query JSON arrays in a similar way we do with JSON objects.

Query:

INSERT INTO products (name, details)
VALUES (
'Laptop',
'{"brand": "TechCorp", "model": "X200", "features": {"ports": ["USB-C", "HDMI", "Ethernet"]}}'::jsonb
);

Output:

Explanation:

This query inserts a new product Laptop with various ports into the products table. The ports field is a JSON array stored in the details column.

JSON Arrays Using JSON Operators

This query uses the @> operator to check if the details JSONB column contains a specific value within its nested array. It filters for products that have "USB-C" listed under the "ports" in the "features" object. The query will return the name of products where this condition is met.

Query:

SELECT name
FROM products
WHERE details @> '{"features": {"ports": ["USB-C"]}}'::jsonb;

Output:

Explanation:

This query returns the name of products where the ports array in the features field containing "USB-C". The output shows that the Laptop product meets this condition.

Conclusion

PostgreSQL's JSONB data type allows for efficient storage and processing of JSON data, offering significant advantages in performance and flexibility over the standard JSON type. It is particularly beneficial for applications that require quick access to structured data, such as APIs and large data sets.

What Is Polardb for Postgresql?

om7826pw5al

Improve

Article Tags :

What is JSONB in PostgreSQL?

Understanding JSON

JSONB in PostgreSQL

Creating a Products Table with JSONB

Overview of JSON Functions and Operators

Retrieving and Querying JSONB Data

Working with JSON Arrays in PostgreSQL

JSON Arrays Using JSON Operators

Conclusion

Similar Reads