BrevFeed
Sign in

45. JSON and JSONB

📖 Reading · 7 min

Why a relational database has a JSON type at all

Real applications constantly deal with genuinely semi-structured data — a third-party API's response payload, a flexible "settings" blob, event data with a shape that varies by event type. Forcing every possible field into its own normalized column (Module 8) is sometimes the wrong tradeoff — Postgres's JSON support lets you keep that data queryable without fully normalizing it, when normalization genuinely isn't the right fit (a real design decision, not a default escape hatch from proper modeling).

JSON vs JSONB — pick JSONB, almost always

Postgres has two JSON types:

  • json — stores the exact text you inserted, byte for byte (including whitespace and key order), and re-parses it on every access. Faster to insert (no parsing up front), slower to query.
  • jsonb — stores a parsed, binary representation (no whitespace, keys de-duplicated, order not preserved). Slightly slower to insert (parsing happens up front), substantially faster to query, and — critically — the only one that supports indexing.

Default to jsonb unless you have a specific reason to preserve the exact original text (e.g. an audit log that must store the literal payload byte-for-byte). Every example in this lesson uses jsonb, and so should the overwhelming majority of real schemas.

The core operators

CREATE TABLE event_log (id serial PRIMARY KEY, payload jsonb);
INSERT INTO event_log (payload) VALUES
    ('{"event": "rental_created", "customer_id": 1, "meta": {"source": "web", "retries": 0}}');
  • -> — get a JSON value as JSON (keeps nesting, useful for chaining or returning JSON): sql SELECT payload -> 'meta' FROM event_log; -- {"source": "web", "retries": 0} SELECT payload -> 'meta' -> 'source' FROM event_log; -- "web" (still JSON, quoted)
  • ->> — get a JSON value as text (unwraps to a plain SQL value — what you almost always want for filtering/comparing): sql SELECT payload ->> 'event' FROM event_log; -- rental_created (plain text, unquoted) SELECT payload -> 'meta' ->> 'source' FROM event_log; -- web (chain -> for nesting, ->> for the final unwrap)
  • @> — "contains" — does the left JSON value contain the right one as a subset? The core operator for filtering and the one GIN indexing (below) accelerates: sql SELECT * FROM event_log WHERE payload @> '{"event": "rental_created"}'; SELECT * FROM event_log WHERE payload @> '{"meta": {"source": "web"}}'; -- nested containment works too

A common mistake worth flagging directly: filtering with ->> and = works, but @> is almost always the better choice for equality-style filters on JSONB, both for readability once nesting gets deep and because it's the operator that can actually use a GIN index (below) — payload ->> 'event' = 'rental_created' cannot use a plain GIN index the same way.

Indexing JSONB with GIN

Recall Module 10's GIN lesson: "does this row contain X" is exactly GIN's specialty, and JSONB containment (@>) is exactly that kind of question:

CREATE INDEX idx_event_log_payload ON event_log USING gin (payload);

EXPLAIN SELECT * FROM event_log WHERE payload @> '{"event": "rental_created"}';
-- Eligible to use idx_event_log_payload, same selectivity caveats from
-- Module 10 apply -- the planner still has to judge it's actually worth
-- using on a given table size.

A default GIN index on a jsonb column indexes every key and value in the document, supporting @> queries against any nesting level — convenient, but larger than necessary if you only ever query a few specific keys. A jsonb_path_ops GIN index is a narrower, smaller alternative that supports @> more efficiently but not a couple of other JSONB operators (?, key-existence) — worth knowing exists as a targeted optimization once a plain jsonb GIN index's size becomes a real concern (an operational-tuning territory beyond this course's scope).

Constructing and modifying JSONB

-- Building JSON from SQL values.
SELECT jsonb_build_object('film_id', film_id, 'title', title) FROM film LIMIT 3;

-- Updating one key without rewriting the whole document.
UPDATE event_log SET payload = jsonb_set(payload, '{meta,retries}', '1') WHERE id = 1;

-- Removing a key.
UPDATE event_log SET payload = payload - 'meta' WHERE id = 1;

jsonb_set(target, path, new_value) takes a path array ('{meta,retries}' means "the retries key inside the meta key") — this is how you surgically update nested JSON without reconstructing the entire document in application code.

Check yourself

  1. What's the practical difference between json and jsonb, and which should you default to?
  2. What does -> return that ->> does not?
  3. Why is @> generally preferred over ->> ... = for JSONB filtering, beyond just being shorter to write?
← Previous Next →