🗂️Indexing JSON

Make hot paths sargable

Raw JSON scans do not scale. Expression B-trees target stable scalar extractions; GiN (with jsonb_path_ops on PostgreSQL) accelerates containment (@>) at the cost of operator support and build size. Generated columns can mirror hot keys for simpler plans.

SQL

B-tree on expression

CREATE INDEX ix_profiles_mode
ON instrument_profiles ((settings->>'mode'));

✓Illustrative result set

result
CREATE INDEX

SQL

GiN with jsonb_path_ops (smaller, faster @>)

CREATE INDEX ix_profiles_settings_gin
ON instrument_profiles USING gin (settings jsonb_path_ops);

✓Illustrative result set

result
CREATE INDEX

⚡ Advanced / dialect note

Over-indexing paths that change on every write amplifies insert and autovacuum cost, promote stable hot filters to real columns when they become first-class predicates.

Index everything in the blob

GiN on whole documents when only one path is queried, paying inverted index cost everywhere.
Volatile expressions inside index definitions, constant rebuild churn.
Oversized documents during concurrent index builds exhausting work memory.

Few paths, measured wins

Benchmark @> vs expression extraction for your top queries.
Materialize stable keys with generated columns when the engine supports it cleanly.
Version JSON contracts next to index DDL in migrations; watch bloat on append-only logs.

📐Operator classes

GIN is not one generic knob.

Default jsonb_ops supports more operators than jsonb_path_ops but grows larger, pick the class that matches predicates you actually issue, not every tutorial snippet.

Portability

MySQL JSON secondary indexes and SQL Server computed-column indexes differ from Postgres GiN, treat patterns as families, not copy-paste DDL across vendors.

TL;DR

JSON indexes make containment/path predicates fast by indexing extracted keys or expression outputs (e.g. PostgreSQL jsonb_path_ops GiN, MySQL generated columns, SQL Server computed columns). They are not a substitute for relational keys, index only the paths you filter on, and remember that large JSON documents inflate storage and vacuum/fragmentation work.