JSON & JSONB — A Document Database Inside a Relational One

postgres1-3 closed by drawing a line: arrays, ranges, and true ENUMs are all still structured and relational. This chapter crosses that line — JSONB is genuinely semi-structured, document-shaped data living inside a relational column, and it's the feature that makes Postgres's own "JSON-heavy hybrid relational/document workloads" entry from postgres1-1's comparison table make real sense.

JSON vs. JSONB

Postgres has two distinct JSON types. json stores an exact textual copy of the input — preserving whitespace, key order, and even duplicate keys — and is re-parsed on every query. jsonb stores a decomposed binary representation instead: no whitespace or key-order preservation, but considerably faster to query, and — critically — indexable.

Practical guidance: use jsonb for nearly everything. json is really only worth reaching for when byte-for-byte preservation of the original document matters, such as an audit log that needs to store exactly what was received.

JSONB Operators

A concrete example where this genuinely earns its keep: a product catalog where a shirt has size/color and a laptop has ram/storage — wildly different fields per category, impossible to model cleanly with fixed relational columns without either a huge, mostly-empty sparse table or an entity-attribute-value (EAV) design, both worse options in their own way.

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

INSERT INTO products (name, category, attributes)
VALUES ('Blue T-Shirt', 'apparel', '{"size": "M", "color": "blue"}'),
       ('ThinkPad X1', 'laptop', '{"ram_gb": 32, "storage_gb": 1024}');

-- Find every laptop with at least 16GB RAM
SELECT name FROM products
WHERE category = 'laptop' AND (attributes ->> 'ram_gb')::int >= 16;

Indexing JSONB — GIN Indexes

A JSONB column can be indexed with a GIN (Generalized Inverted Index), making containment and key-existence queries genuinely fast at real scale:

CREATE INDEX idx_attrs ON products USING GIN (attributes);

This is the concrete payoff distinguishing Postgres's own JSONB support from a plain "store JSON as a text blob" implementation — the database can query into the structure with real index support, not just store and retrieve an opaque document.

Revisiting mongodb1-1's Own Document-vs-Relational Framing

mongodb1-1 introduced the fundamental document-vs-relational distinction, and the question of when MongoDB's own model fits better than MySQL's. JSONB is the point where that clean dividing line gets genuinely blurry: a single Postgres table can have strictly relational columns — foreign keys, indexed scalars with real integrity constraints — alongside one or more JSONB columns behaving like an embedded, schema-flexible sub-document, all in the same row, inside the same ACID-transactional engine.

This doesn't mean Postgres replaces MongoDB — a dedicated document database still has real advantages at massive horizontal scale, and a genuinely schema-less domain still fits MongoDB's own design center better. But for the extremely common real-world case of "mostly relational data, with a few genuinely variable fields," JSONB lets one Postgres database serve both needs at once, without the added complexity of keeping two separate database systems in sync.

Hands-On Exercises