BrevFeed
Sign in

45. JSON and JSONB

📖 Reading · 10 min
💡 Most code boxes below are live — edit one and hit Run. Boxes without a Run button are reference-only (they can't run in your browser).

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.

Try it

The playground's setup seeds a small event_log (the in-browser engine has a JSON type rather than Postgres's json/jsonb pair — the ->/->> operators work the same way). Select id, the event name as text (event), and the nested meta.source as text (source) — but only for events whose meta.source is 'web', ordered by id. Mind the -> vs ->> distinction: chain with ->, unwrap the final hop with ->>.

CREATE TABLE event_log (id INTEGER, payload JSON);
INSERT INTO event_log VALUES
  (1, '{"event": "rental_created",  "customer_id": 1, "meta": {"source": "web", "retries": 0}}'),
  (2, '{"event": "payment_received","customer_id": 1, "meta": {"source": "api", "retries": 1}}'),
  (3, '{"event": "rental_returned", "customer_id": 2, "meta": {"source": "web", "retries": 0}}');

-- id, event (text), source (text) for web-sourced events, by id
SELECT id, ...
FROM event_log
WHERE ...
ORDER BY id;

SELECT id,
    payload ->> 'event' AS event,
    payload -> 'meta' ->> 'source' AS source
FROM event_log
WHERE payload -> 'meta' ->> 'source' = 'web'
ORDER BY id;

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 →