JSONB Deep Dive

JSON vs JSONB — Core Difference

PostgreSQL has two JSON types. Understanding the difference is the foundation of everything in this topic.

When Should You Use JSONB?

JSONB is powerful — but not a replacement for relational design. Use it when the data is truly semi-structured or variable.

Creating a Table with JSONB

The Four Core Extraction Operators

These operators let you dig into a JSONB value to retrieve nested fields.

Using Operators in Real Queries

Containment and Existence Operators

These operators let you check whether a JSONB value contains another, or whether a key exists. Critical for filtering and GIN index usage.

Other Useful Operators

🧪 Interactive: Operator Explorer

Why JSONB Needs Indexes

Without an index, filtering on a JSONB column does a full sequential scan — reads every row and inspects the JSON. On a table with 10M rows, this is catastrophically slow.

GIN Index — The Default Choice

GIN (Generalized Inverted Index) indexes every key-value pair inside the JSONB document. Think of it like a book index — it builds a map of "where does key X with value Y appear?"

GIN Operator Classes

PostgreSQL has two GIN operator classes for JSONB. Choose based on your use case:

Expression Indexes on JSONB (B-Tree)

If you always query a specific key inside JSONB, a regular B-Tree expression index is often faster and smaller than GIN.

Choosing the Right Index Strategy

Verify Index is Being Used

What Is the Hybrid Pattern?

The core idea: use relational columns for structured, known, frequently-queried data, and JSONB for flexible, variable, or extensible data. Never go full-JSON when you have structured fields.

-- Don't put everything in JSON
CREATE TABLE users (
  id   SERIAL PRIMARY KEY,
  data JSONB  -- name, email, role all in here!
);

-- Problems:
-- Can't index email properly
-- Can't enforce NOT NULL on name
-- Can't use foreign keys
-- Poor query performance

-- Fixed fields → columns
-- Variable fields → JSONB
CREATE TABLE users (
  id          SERIAL PRIMARY KEY,
  email       TEXT UNIQUE NOT NULL,
  name        TEXT NOT NULL,
  role        TEXT NOT NULL,
  preferences JSONB  -- theme, notifications, etc.
);

-- Best of both worlds!

Real-World Example: SaaS User with Preferences

Real-World Example: E-Commerce Product Catalog

The classic use case — every category has different attributes but shares a common structure.

Real-World Example: Audit Log / Event Tracking

Store events with a fixed schema + flexible payload — perfect for analytics, audit trails, and webhook logs.

Real-World Example: AI / RAG System with JSONB Metadata

A common pattern in modern AI platforms — documents with metadata stored as JSONB.

Decision Framework — Column vs JSONB

🧪 Interactive: Hybrid Design Advisor

Building JSON from Relational Data

PostgreSQL can build JSON on the fly from relational tables — perfect for APIs.

Generated Columns from JSONB

Extract a JSONB key into a real column automatically — best of both worlds: JSONB flexibility + column performance.

JSONB with CHECK Constraints (Basic Validation)

Querying Across Nested Arrays (Lateral Join)

When JSONB contains an array and you need to filter or join on elements inside it, use LATERAL with jsonb_array_elements.

JSONB Path Queries (jsonpath — PostgreSQL 12+)

PostgreSQL 12 added SQL/JSON path language — a powerful way to query deep into JSONB with conditions.

Mistake 1 — Using ->> Instead of @> for GIN Queries

-- ->> returns text, GIN can't help here
-- Results in a sequential scan!
SELECT * FROM products
WHERE attributes->>'ram' = '16GB';

-- @> containment uses GIN index
SELECT * FROM products
WHERE attributes @> '{"ram":"16GB"}';

Mistake 2 — Putting Relational Data in JSONB

-- user_id in JSONB = can't use FK, slow JOINs
INSERT INTO orders (data) VALUES (
  '{"user_id": 42, "total": 99.99}'
);

-- user_id as a proper FK column
INSERT INTO orders (user_id, total, metadata)
VALUES (42, 99.99,
  '{"promo_code":"SAVE10"}');

Mistake 3 — Forgetting to Cast JSONB Numbers

-- "9" > "10" as text! Wrong result.
WHERE attributes->>'count' > '10'

-- Cast to numeric first
WHERE (attributes->>'count')::numeric > 10

Mistake 4 — No Index on Frequently Queried JSONB

Mistake 5 — Over-using JSONB (JSON for Everything)

🎯 Quick Check — Test Your Understanding

Operator Cheatsheet

Key Rules to Remember

What's Next?