How does JSONB work and when should you use it?

Question

IT Interview Practice · Accepted Answer

**`JSONB`** is PostgreSQL's binary JSON type — it stores JSON data in an efficient, **queryable, and indexable** format. It lets you store flexible, semi-structured data in a relational database, combining relational integrity with NoSQL-like flexibility. It's one of Postgres's most powerful features.

## JSONB vs JSON

```text
JSON  → stores the exact text (preserves whitespace/key order/duplicates), parsed each query
JSONB → stores a DECOMPOSED BINARY format → slightly slower to write, but MUCH faster
        to query, and supports INDEXING and rich operators
→ Use JSONB in almost all cases (JSON only if you need exact text preservation).
```

## Storing and querying JSONB

```sql
CREATE TABLE products (id SERIAL, attributes JSONB);
INSERT INTO products (attributes) VALUES
  ('{"color": "red", "size": "L", "tags": ["new", "sale"]}');

-- extract values
SELECT attributes->'color'   FROM products;   -- -> returns JSONB: "red"
SELECT attributes->>'color'  FROM products;   -- ->> returns TEXT: red
SELECT attributes#>>'{tags,0}' FROM products; -- nested path: first tag

-- query by content
SELECT * FROM products WHERE attributes->>'color' = 'red';
SELECT * FROM products WHERE attributes @> '{"color": "red"}';  -- containment
SELECT * FROM products WHERE attributes ? 'color';              -- key exists
```

Operators: `->` (get JSONB field), `->>` (get as text), `@>` (containment), `?` (key exists), `#>>` (nested path). These make JSONB richly queryable — unlike storing JSON as plain text.

## Indexing JSONB (key to performance)

```sql
-- a GIN index makes containment/key queries fast
CREATE INDEX idx_attrs ON products USING GIN (attributes);
-- now WHERE attributes @> '{"color": "red"}' uses the index

-- or index a specific extracted field
CREATE INDEX idx_color ON products ((attributes->>'color'));
```

## When to use JSONB (and when not)

```text
✓ USE for: flexible/variable attributes, settings, API payloads, sparse fields,
  semi-structured data that varies per row, or rapidly-evolving schemas
✗ AVOID for: data that's well-structured and queried relationally → use normal columns
  (better constraints, types, and typically better performance for structured queries)
→ Mix both: structured data in columns, variable/flexible data in a JSONB column.
```

## Why it matters

JSONB is one of PostgreSQL's most important and distinctive features, and understanding it is highly valuable because it lets you **store and efficiently query semi-structured data within a relational database** — combining the integrity, transactions, and relational power of Postgres with the flexibility of a document/NoSQL store, which is a major capability for modern applications.

The key advantage over storing JSON as plain text (or `JSON` type) is that **JSONB is queryable and indexable**: rich operators (`->`, `->>`, `@>`, `?`) let you extract and filter on JSON content, and **GIN indexes** (or indexes on extracted fields) make these queries fast — so you get document-store-like flexibility without sacrificing query performance.

This makes JSONB ideal for flexible or variable attributes, settings, API payloads, sparse data, and evolving schemas — common modern needs that would otherwise require a NoSQL database or awkward schema designs.

Understanding the **JSONB vs JSON** distinction (JSONB for almost all cases), the query operators, **indexing** (essential for performance), and crucially **when to use JSONB versus normal columns** (JSONB for flexible/variable/semi-structured data, normal columns for well-structured relationally-queried data — often mixing both) reflects mature design judgment.

Since the ability to handle flexible, semi-structured data is increasingly important, and since JSONB provides this within a robust relational database (often eliminating the need for a separate NoSQL store), understanding JSONB — its querying, indexing, and appropriate use — is valuable, frequently-relevant knowledge that's a key reason PostgreSQL is so popular for modern applications and a hallmark of leveraging Postgres's full power.

JSONB vs JSON

Storing and querying JSONB

Indexing JSONB (key to performance)

When to use JSONB (and when not)

Why it matters

Related questions