postgres://internals

JSON & JSONB

// the problem

Postgres is a genuinely good document database hiding inside a relational one. You can drop a whole JSON object into a column, query inside it with operators, and — the part people miss — index it so those queries stay fast at scale. But there are two JSON types with very different performance, and querying JSON has its own small grammar. Let's make all of it concrete, live.

Below are five event documents. Pick an operator and watch which rows match — this is exactly the shape-matching Postgres does inside a jsonb column.

jsonb · query the shape2 / 5 rows match

WHERE containment — the row's JSON must include this key/value pair.

✓{"user":"alice","action":"login","tags":["web","eu"],"meta":{"ip":"10.0.0.1"}}
·{"user":"bob","action":"purchase","amount":42,"tags":["mobile"]}
✓{"user":"carol","action":"login","tags":["web","us"]}
·{"user":"dave","action":"logout","tags":["web"],"meta":{"ip":"10.0.0.9"}}
·{"user":"erin","action":"purchase","amount":0,"tags":["mobile","eu"]}

containment (@>) and key-existence (?) are exactly what a GIN index accelerates.

Now the real thing. This creates a table with a jsonb column and a handful of readable rows we'll query throughout:

sql · live postgresrun me first — creates the events table

DROP TABLE IF EXISTS events;
CREATE TABLE events (id int, payload jsonb);
INSERT INTO events VALUES
(1, '{"user":"alice","action":"login","tags":["web","eu"],"meta":{"ip":"10.0.0.1"}}'),
(2, '{"user":"bob","action":"purchase","amount":42,"tags":["mobile"]}'),
(3, '{"user":"carol","action":"login","tags":["web","us"]}'),
(4, '{"user":"dave","action":"logout","tags":["web"],"meta":{"ip":"10.0.0.9"}}'),
(5, '{"user":"erin","action":"purchase","amount":0,"tags":["mobile","eu"]}');

SELECT * FROM events ORDER BY id;

DROP TABLE IF EXISTS events;
CREATE TABLE events (id int, payload jsonb);
INSERT INTO events VALUES
(1, '{"user":"alice","action":"login","tags":["web","eu"],"meta":{"ip":"10.0.0.1"}}'),
(2, '{"user":"bob","action":"purchase","amount":42,"tags":["mobile"]}'),
(3, '{"user":"carol","action":"login","tags":["web","us"]}'),
(4, '{"user":"dave","action":"logout","tags":["web"],"meta":{"ip":"10.0.0.9"}}'),
(5, '{"user":"erin","action":"purchase","amount":0,"tags":["mobile","eu"]}');

SELECT * FROM events ORDER BY id;

⌘/ctrl + enter

`json` vs `jsonb` — always reach for `jsonb`

Postgres has two JSON types, and the difference is not cosmetic:

json stores an exact text copy — it preserves whitespace, key order, and even duplicate keys. Every read re-parses the text. No indexing of the contents.
jsonb stores a decomposed binary form — whitespace is dropped, keys are sorted and de-duplicated, and it can be indexed and queried with the full operator set. Slightly slower to write, far faster to query.

See it — same input, cast to each type, shown as text:

sql · live postgreseditable — run it

SELECT '{"b":1,  "a":2}'::json::text  AS as_json,   -- kept verbatim
     '{"b":1,  "a":2}'::jsonb::text AS as_jsonb;  -- normalized & reordered

SELECT '{"b":1,  "a":2}'::json::text  AS as_json,   -- kept verbatim
     '{"b":1,  "a":2}'::jsonb::text AS as_jsonb;  -- normalized & reordered

⌘/ctrl + enter

// why it matters · use jsonb unless you have a specific reason

The only time you want plain json is when you must round-trip the exact original text (whitespace, key order, duplicates) byte-for-byte. For everything else — querying, indexing, updating — jsonb is the right default.

Reaching inside: `->`, `->>`, and paths

Two arrows do most of the work. -> returns the value as jsonb; ->> returns it as text. The double-arrow is what you want when you need a plain string (to compare, cast, or display):

sql · live postgreseditable — run it

SELECT payload->'user'   AS as_jsonb,   -- "alice" (still jsonb)
     payload->>'user'  AS as_text,    -- alice   (text)
     payload->'tags'->>0 AS first_tag -- index into an array
FROM events WHERE id = 1;

SELECT payload->'user'   AS as_jsonb,   -- "alice" (still jsonb)
     payload->>'user'  AS as_text,    -- alice   (text)
     payload->'tags'->>0 AS first_tag -- index into an array
FROM events WHERE id = 1;

⌘/ctrl + enter

For nested values, #> / #>> take a path so you don't chain arrows:

sql · live postgreseditable — run it

SELECT payload#>>'{meta,ip}' AS ip   -- text at path meta → ip
FROM events WHERE id = 1;

SELECT payload#>>'{meta,ip}' AS ip   -- text at path meta → ip
FROM events WHERE id = 1;

⌘/ctrl + enter

Querying the shape: containment and keys

This is where jsonb earns its keep. Instead of extracting a field and comparing, you ask whether the document contains a shape:

@> — containment. payload @> '{"action":"login"}' is true when the left document includes every key/value on the right (recursively).
? — does a top-level key (or array string element) exist?
?| / ?& — does any / all of these keys exist?

sql · live postgreseditable — run it

-- everyone who logged in (containment)
SELECT id, payload->>'user' AS "user"
FROM events WHERE payload @> '{"action":"login"}' ORDER BY id;

-- rows that carry an amount field at all
SELECT id FROM events WHERE payload ? 'amount' ORDER BY id;

-- rows tagged for the EU or US region
SELECT id FROM events WHERE payload->'tags' ?| array['eu','us'] ORDER BY id;

-- everyone who logged in (containment)
SELECT id, payload->>'user' AS "user"
FROM events WHERE payload @> '{"action":"login"}' ORDER BY id;

-- rows that carry an amount field at all
SELECT id FROM events WHERE payload ? 'amount' ORDER BY id;

-- rows tagged for the EU or US region
SELECT id FROM events WHERE payload->'tags' ?| array['eu','us'] ORDER BY id;

⌘/ctrl + enter

// note · containment is the workhorse

@> is the operator you'll reach for most, and — crucially — it's the one a GIN index accelerates. Learn to phrase filters as containment and your JSON queries stay indexable.

// challengewrite it yourself

Using the containment operator @>, count how many events have action 'login'.

SELECT count(*) AS count
FROM events
WHERE payload @> '{"action":"..."}';

SELECT count(*) AS count
FROM events
WHERE payload @> '{"action":"..."}';

⌘/ctrl + enter

Indexing JSON: the GIN index

A jsonb column is invisible to a normal B-tree — @> and ? can't use one. The right tool is a GIN index (Generalized INverted iNdex): it stores an entry for every key and value inside every document, so it can answer "which rows contain this?" without scanning the table.

Let's prove it at scale. This builds 40,000 log rows where action = 'login' is rare (1 in 400):

sql · live postgresrun me first — the large logs table

DROP TABLE IF EXISTS logs;
CREATE TABLE logs (id int, doc jsonb);
INSERT INTO logs
SELECT g,
jsonb_build_object(
  'user', 'u' || g,
  'action', CASE WHEN g % 400 = 0 THEN 'login' ELSE 'noise' END)
FROM generate_series(1, 40000) AS g;
ANALYZE logs;
SELECT count(*) AS logins FROM logs WHERE doc @> '{"action":"login"}';

DROP TABLE IF EXISTS logs;
CREATE TABLE logs (id int, doc jsonb);
INSERT INTO logs
SELECT g,
jsonb_build_object(
  'user', 'u' || g,
  'action', CASE WHEN g % 400 = 0 THEN 'login' ELSE 'noise' END)
FROM generate_series(1, 40000) AS g;
ANALYZE logs;
SELECT count(*) AS logins FROM logs WHERE doc @> '{"action":"login"}';

⌘/ctrl + enter

With no index, containment must read every row — a Seq Scan:

sql · live postgreseditable — run it

EXPLAIN SELECT id FROM logs WHERE doc @> '{"action":"login"}';

EXPLAIN SELECT id FROM logs WHERE doc @> '{"action":"login"}';

⌘/ctrl + enter

Now add a GIN index and ask again — the plan flips to a Bitmap Index Scan:

sql · live postgresbuild the GIN index, then re-explain

CREATE INDEX IF NOT EXISTS logs_gin ON logs USING gin (doc);
ANALYZE logs;
EXPLAIN SELECT id FROM logs WHERE doc @> '{"action":"login"}';

CREATE INDEX IF NOT EXISTS logs_gin ON logs USING gin (doc);
ANALYZE logs;
EXPLAIN SELECT id FROM logs WHERE doc @> '{"action":"login"}';

⌘/ctrl + enter

// why it matters · jsonb_ops vs jsonb_path_ops

The default GIN operator class (jsonb_ops) indexes every key and every value, supporting ?, ?|, ?&, and @>. If you only ever use @>, the jsonb_path_ops class (USING gin (doc jsonb_path_ops)) indexes hashed paths instead — a smaller, faster index that supports containment only. Pick it when containment is all you need.

One field, hot? Use an expression index instead

GIN is for querying arbitrary keys. If you always filter or sort on one extracted field, a plain B-tree over an expression is smaller and supports ranges and ordering:

sql · live postgresa B-tree on one extracted key

CREATE INDEX IF NOT EXISTS logs_user ON logs ((doc->>'user'));
ANALYZE logs;
EXPLAIN SELECT id FROM logs WHERE doc->>'user' = 'u12345';

CREATE INDEX IF NOT EXISTS logs_user ON logs ((doc->>'user'));
ANALYZE logs;
EXPLAIN SELECT id FROM logs WHERE doc->>'user' = 'u12345';

⌘/ctrl + enter

That's an Index Scan on the expression index — note the double parentheses around (doc->>'user'), which are required for an expression index.

// note · two tools, two jobs

GIN — “which rows contain this key/value?”, many different keys, @>/?. Expression B-tree — one known field, equality and ranges/sorting. Real schemas often have both on the same column.

Modifying JSON

jsonb has a full toolkit for building and editing documents — all producing new values (Postgres never mutates in place):

sql · live postgreseditable — run it

SELECT
jsonb_set(payload, '{action}', '"seen"')      AS updated,   -- set a key
payload || '{"flagged":true}'                 AS merged,    -- shallow merge
payload - 'tags'                              AS without_tags -- drop a key
FROM events WHERE id = 1;

SELECT
jsonb_set(payload, '{action}', '"seen"')      AS updated,   -- set a key
payload || '{"flagged":true}'                 AS merged,    -- shallow merge
payload - 'tags'                              AS without_tags -- drop a key
FROM events WHERE id = 1;

⌘/ctrl + enter

And you can explode arrays and objects back into rows — turning a document into a relation you can aggregate:

sql · live postgreseditable — run it

SELECT id, jsonb_array_elements_text(payload->'tags') AS tag
FROM events ORDER BY id;

SELECT id, jsonb_array_elements_text(payload->'tags') AS tag
FROM events ORDER BY id;

⌘/ctrl + enter

// gotcha · don't put everything in one jsonb blob

jsonb is for data whose shape is genuinely variable or sparse. When the shape is stable, real columns win: they're smaller, they enforce types and constraints, they index simply, and the planner has statistics on them. A table that is one giant data jsonb column throws away most of what a relational database gives you. Reach for jsonb at the edges, not the core.

Your turn

The logs table (from the cell above) has 40,000 rows.

// challengewrite it yourself

Index logs.doc for containment with a GIN index, then EXPLAIN a containment query for action 'login' and confirm the plan uses the index (a Bitmap Index Scan).

CREATE INDEX IF NOT EXISTS logs_gin ON logs USING gin (doc);
ANALYZE logs;
EXPLAIN SELECT id FROM logs WHERE doc @> '{"action":"login"}';

CREATE INDEX IF NOT EXISTS logs_gin ON logs USING gin (doc);
ANALYZE logs;
EXPLAIN SELECT id FROM logs WHERE doc @> '{"action":"login"}';

⌘/ctrl + enter

// what you now understand

01Prefer jsonb over json: it's parsed, normalized, indexable, and has the full operator set; json only preserves exact original text.
02Extract with -> (returns jsonb) and ->> (returns text); use #> / #>> for a path into nested values.
03Query the shape with containment @> and key-existence ? / ?| / ?& — phrase filters as containment to keep them indexable.
04A GIN index makes @> / ? fast on jsonb; jsonb_path_ops is a smaller containment-only variant.
05For one hot extracted field, a B-tree expression index ((doc->>'k')) is smaller and supports ranges and ordering.
06jsonb_set / || / - build and edit documents; jsonb_array_elements explodes arrays into rows.
07Use jsonb for genuinely variable shapes — when the shape is stable, real columns are smaller, typed, and better-optimized.

// self-test

You filter a large jsonb table with WHERE payload @> '{"status":"active"}' and it's slow (Seq Scan). What's the fix?

// self-test

What does payload->>'user' return, versus payload->'user'?

// go deeper

JSON Types (official docs) — json vs jsonb, and the containment/existence semantics
JSON Functions and Operators — the full operator and function reference
jsonb Indexing — GIN, jsonb_ops vs jsonb_path_ops, expression indexes
GIN Indexes — how the inverted index works

nextMVCC & Transactions

JSON & JSONB

json vs jsonb — always reach for jsonb

Reaching inside: ->, ->>, and paths

Querying the shape: containment and keys

Indexing JSON: the GIN index

One field, hot? Use an expression index instead

Modifying JSON

Your turn

`json` vs `jsonb` — always reach for `jsonb`

Reaching inside: `->`, `->>`, and paths