What is jsonb in PostgreSQL: Explanation and Usage

jsonb in PostgreSQL is a data type that stores JSON data in a binary format, allowing fast access and efficient querying. Unlike plain json, jsonb stores data in a decomposed binary form, which makes it faster for searching and indexing.

How It Works

Think of jsonb as a smart container for JSON data inside PostgreSQL. Instead of keeping the JSON as plain text, it breaks it down into a binary format that the database can understand and search quickly. This is like having a neatly organized filing cabinet instead of a messy pile of papers.

This binary format allows PostgreSQL to index parts of the JSON data, making queries faster and more efficient. When you ask the database to find something inside a jsonb column, it doesn’t have to scan the whole text but can jump directly to the relevant parts.

Because of this, jsonb supports many operators and functions to query and manipulate JSON data easily, making it very powerful for applications that need flexible data storage with fast access.

Example

This example shows how to create a table with a jsonb column, insert JSON data, and query it.

sql

CREATE TABLE products (
  id SERIAL PRIMARY KEY,
  info jsonb
);

INSERT INTO products (info) VALUES
  ('{"name": "Laptop", "price": 1200, "features": ["16GB RAM", "512GB SSD"]}'),
  ('{"name": "Phone", "price": 800, "features": ["OLED screen", "128GB storage"]}');

-- Query products with price greater than 1000
SELECT info->>'name' AS product_name, info->>'price' AS price
FROM products
WHERE (info->>'price')::int > 1000;

Output

 product_name | price 
--------------+-------
 Laptop       | 1200
(1 row)

When to Use

Use jsonb when you need to store flexible or semi-structured data that can change over time, like user preferences, product details, or logs. It is ideal when you want to combine the power of a relational database with the flexibility of JSON.

It is especially useful when you want to query parts of the JSON data efficiently or create indexes on JSON keys. For example, an e-commerce site can store product attributes in jsonb to handle different categories without changing the table schema.

Key Points

• jsonb stores JSON data in a binary, decomposed format for fast access.
• Supports indexing and efficient querying of JSON fields.
• Allows flexible schema design inside a relational database.
• Better performance than plain json for most operations.

Key Takeaways

jsonb stores JSON data in a binary format for fast querying and indexing.

It is ideal for flexible, semi-structured data that changes over time.

Use jsonb to combine relational and document-style data in PostgreSQL.

Queries on jsonb are faster than on plain json due to its internal structure.