Part 2 — Querying › Chapter 18

JSON and semi-structured data

When a JSON column is the right answer, when it is a schema you refused to design, and how to index one.

No advert asked for this

Every engine grew JSON support and every legacy schema now has a “settings” column. Nobody advertises it; everybody meets it.

When a JSON column is the right answer

🧒 Simple

A filing cabinet has a drawer for each kind of thing, and that is why you can find anything. A JSON column is a drawer labelled “miscellaneous”. Sometimes you genuinely need one. But if everything ends up in it, you do not have a filing cabinet any more — you have a box.

🎓 Professional

Three cases where a JSON column is a good decision:

Genuinely open-ended attributes. Per-tenant custom fields, where the set of keys is defined by customers and not by you.
A payload you received and must keep verbatim. A webhook body, an API response, an audit snapshot. You store it to prove what arrived, and you query the columns you extracted from it — not the blob.
A sparse, rarely-queried settings bag. Forty optional preferences that are read as a unit and never filtered on.

And the case where it is not: a field you filter, join or aggregate on. That is a column. If you find yourself indexing a JSON path because a query needs it, the schema is telling you something.

⚠ Trap

A JSON column has no constraints. No type checking, no NOT NULL, no foreign key, no uniqueness. Every value in it is “whatever the application wrote last Tuesday”, including the typo'd key name that has been silently accumulating for six months. The relational engine cannot help you, which is the entire point of having chosen one.

Reading it, four ways

Reading a value out of a JSON document, in the four dialects you will meet.

-- PostgreSQL (jsonb — the binary, indexable one; prefer it to json)
SELECT data ->> 'email'                    AS email,   -- ->> returns TEXT, -> returns jsonb
       (data -> 'prefs' ->> 'lang')        AS lang,    -- chain -> then ->> at the last step
       jsonb_array_length(data -> 'tags')  AS n_tags   -- array length, as an integer
FROM events                                            -- one row per event document
WHERE data @> '{"type":"signup"}';                     -- @> is containment, and GIN can index it

-- SQL Server 2016+
SELECT JSON_VALUE(data, '$.email')          AS email,  -- a SCALAR; NULL if the path is an object
       JSON_QUERY(data, '$.prefs')          AS prefs   -- an object or array; NULL for a scalar
FROM events WHERE ISJSON(data) = 1;                    -- the column is text, so validate it

-- MySQL 5.7+
SELECT data ->> '$.email' AS email FROM events;        -- same operator, path syntax instead of key

-- Oracle 12c+
SELECT JSON_VALUE(data, '$.email') FROM events;        -- the same function name as SQL Server

Types, and the conversion that catches everyone

-> returns JSON; ->> returns TEXT. So data -> 'email' = 'a@b.it' is comparing a JSON value to a string and fails or misses, while data ->> 'email' = 'a@b.it' works.
Everything you extract is text until you cast it. (data ->> 'qty')::int > 5 compares numbers; without the cast it compares strings, and '10' < '9' is TRUE.
JSON_VALUE returns NULL rather than raising when the path points at an object — which is exactly why JSON_QUERY exists. Using the wrong one gives you a silent column of NULLs.
In SQL Server the column is ordinary NVARCHAR: nothing guarantees it is JSON until you add CHECK (ISJSON(data) = 1). Add it.

json vs jsonb in PostgreSQL

json stores the exact text, preserving key order and whitespace, and re-parses on every access. jsonb stores a parsed binary form: slightly slower to write, much faster to read, deduplicates keys, loses formatting, and is the only one that can be indexed usefully. Use jsonb unless you must reproduce the original bytes exactly — which is really the "keep the payload verbatim" case, and then a text column is more honest.

Indexing inside a document

Indexing inside a document — whole-document, one path, or a computed column.

-- PostgreSQL: a GIN index supports containment (@>) over the WHOLE document.
CREATE INDEX ix_events_data ON events                    -- one index, any key…
  USING GIN (data jsonb_path_ops);                       -- …smaller, because it indexes @> only

-- Or, far more often what you actually want: ONE extracted path, in a B-tree.
CREATE INDEX ix_events_email ON events ((data ->> 'email'));  -- note the double parentheses

-- SQL Server has no JSON index. Index a computed column instead:
ALTER TABLE events ADD email AS JSON_VALUE(data, '$.email') PERSISTED;  -- stored, so indexable
CREATE INDEX ix_events_email ON events (email);          -- an ordinary index on an ordinary column

Choosing between them

A GIN index is right when queries filter on varying keys — genuinely schemaless data. It is large, slower to update, and only helps the operators it supports.
An expression index on one path is right when one or two keys are queried constantly. The predicate must be written exactly as the index expression, or it is not used — data->>'email' matches, JSON_VALUE(...) does not.
The double parentheses in ((data ->> 'email')) are required: the inner pair says “this is an expression”, the outer pair is the index's column list.
PERSISTED in SQL Server means the computed value is physically stored. Without it the column is recomputed per read and cannot be indexed.
If a key is queried in every request, it is not a document attribute — it is a column. Say that in an interview.

✔ The pattern that actually works in production

Promote the fields you query into real columns — as generated/computed columns derived from the JSON, so there is still one source of truth — and keep the document for everything else. You get constraints, types, statistics and ordinary indexes on the parts that matter, and flexibility on the parts that do not. Describing this in an interview is a much stronger answer than either “JSON is bad” or “we put everything in JSON”.

The best of both: a real, typed, indexable column that cannot drift from the document.

-- PostgreSQL 12+: a stored generated column. Always in step, indexable, typed.
ALTER TABLE events                                 -- change the table definition…
  ADD COLUMN email TEXT                              -- a real column, with a real type…
  GENERATED ALWAYS AS (data ->> 'email') STORED;     -- …whose value is derived, never written
CREATE INDEX ix_events_email ON events (email);      -- and now an ordinary index works

Why this beats both alternatives

GENERATED ALWAYS means an INSERT may not supply the value — so the column cannot disagree with the document. A trigger-maintained copy can, and eventually does.
STORED writes the value physically. It costs disk and a little write time, and it buys an index that behaves exactly like any other.
Queries stay readable: WHERE email = ?, not WHERE data->>'email' = ?. The document remains the source of truth.
Adding the column rewrites the table on most engines. On a large table, do it the way chapter 36 describes rather than in a deploy window.

Turning a document into rows

Shredding: turning a document's array into rows you can join, filter and aggregate.

-- PostgreSQL: expand an array into rows, then treat it as a normal table.
SELECT e.id, t.tag                                          -- one row per (event, tag)
FROM events e                                               -- the documents
CROSS JOIN LATERAL                                          -- once per event row…
  jsonb_array_elements_text(e.data -> 'tags') AS t(tag);    -- …expand its tags array

-- SQL Server:
SELECT e.id, t.value AS tag                                 -- OPENJSON names the column "value"
FROM events e                                               -- the same documents
CROSS APPLY OPENJSON(e.data, '$.tags') AS t;                -- APPLY is SQL Server's LATERAL

-- OPENJSON WITH a schema is how you bulk-load a JSON payload into a table:
INSERT INTO order_lines (order_id, sku, quantity)           -- always name the columns
SELECT @order_id, j.sku, j.qty                              -- @order_id is a parameter, not text
FROM OPENJSON(@payload, '$.lines')                          -- the array of line objects…
     WITH (sku VARCHAR(32) '$.sku',                         -- …declared as typed columns…
           qty INT         '$.quantity') AS j;              -- …so the insert is type-checked

Why the WITH clause matters

Without WITH, OPENJSON returns generic key/value/type columns as text. With it, you declare the shape once and the engine converts and validates — a bad quantity fails at the boundary instead of becoming a wrong number three tables later.
Passing a whole payload as one parameter and shredding it server-side turns a hundred round trips into one statement. It is the single most effective fix for a slow import loop.
@payload is a parameter, so nothing here is string-built and injection is not possible (chapter 34).

The same idea in SQLite — and an index that turns the scan into a search.

-- SQLite (the playground) has the JSON1 functions built in:
SELECT json_extract(data, '$.email')     AS email,    -- returns TEXT for a string path
       json_array_length(data, '$.tags') AS n_tags    -- returns INTEGER
FROM events                                           -- every document is read…
WHERE json_extract(data, '$.type') = 'signup';        -- a function on every row: full scan

-- Now look at EXPLAIN QUERY PLAN. Then create the expression index:
CREATE INDEX ix_ev_type ON events (json_extract(data, '$.type'));  -- index the SAME expression
-- and look again: SCAN becomes SEARCH.

What to notice when you run it

The index expression must match the predicate textually. Change '$.type' to '$.Type' in one of the two places and the index is silently unused — the plan is the only place that tells you.
json_extract returns whatever the JSON held: TEXT for strings, INTEGER for numbers, NULL for a missing path. A missing key and a JSON null both arrive as SQL NULL, so “absent” and “explicitly null” are indistinguishable — design around that, or use json_type().
This is the whole JSON trade-off in one screen: flexibility on the way in, an index you have to ask for on the way out.

The honest summary

🎓 What to say when asked “relational or document?”

“A JSON column is the right tool for genuinely schemaless data inside an otherwise relational system — custom fields, stored payloads, sparse settings. What it costs is every guarantee the database exists to provide: types, constraints, referential integrity and statistics. So I use it for the parts of a record nobody queries, promote anything I filter on into a real column, and treat a JSON column that has grown an index on three different paths as a schema waiting to be written.”

JSON is right for open-ended attributes, verbatim payloads and sparse settings — not for anything you filter, join or aggregate.
A JSON column has no types, no constraints and no foreign keys. Everything the engine could have checked, it now cannot.
PostgreSQL: use jsonb, not json. Only jsonb indexes usefully.
Index one extracted path with a B-tree far more often than the whole document with GIN.
SQL Server has no JSON index; index a PERSISTED computed column instead.
Promote queried fields into generated columns: one source of truth, real indexes and statistics.
Expand arrays into rows with jsonb_array_elements / OPENJSON and then use ordinary SQL.
Three indexes on three JSON paths is a schema asking to be written.

Interview questions this chapter answers

When would you store JSON in a relational database?

When the shape is genuinely defined by someone other than me — per-tenant custom fields — or when I must keep a payload verbatim, or for sparse settings read as a unit. Not for anything I filter or join on: that loses types, constraints and statistics for no benefit.

How do you make a query on a JSON field fast?

Index the specific extracted path rather than the document: an expression index in PostgreSQL, a persisted computed column plus an index in SQL Server. If several paths need indexing, that is a signal to promote them to real columns.

What do you lose by using a JSON column?

Type checking, NOT NULL, uniqueness, foreign keys, column statistics and therefore good cardinality estimates. The engine can no longer prevent bad data or plan well against it — which are the two reasons for using a relational database at all.