JSON in a column: when it helps and what it costs

Attributes that refuse to be columns

A shop sells shirts, chargers and books. A shirt has a size and a colour; a charger has a wattage and a plug type; a book has an author and a page count. A products table with a column for every attribute of every kind of product is mostly NULL and grows a column every time a new kind arrives. A product_attributes table with one row per product per attribute works and makes every query a pivot. The third option is a column that holds structured data:

CREATE TABLE products (
  id     bigint PRIMARY KEY,
  name   text NOT NULL,
  price  numeric(10,2) NOT NULL,
  attrs  jsonb NOT NULL DEFAULT '{}'
);

INSERT INTO products VALUES
  (1, 'Linen shirt', 1800, '{"size": "M", "colour": "white"}'),
  (2, 'USB-C charger', 1200, '{"watts": 65, "plug": "type-D"}');

Postgres’s jsonb stores the document in a parsed binary form and lets queries reach inside it: attrs ->> 'colour' returns the text white, attrs -> 'watts' returns the JSON value, and attrs @> '{"plug": "type-D"}' asks whether the document contains that fragment. MySQL has a JSON type with ->> and JSON_EXTRACT; SQLite has json_extract(attrs, '$.colour'); DuckDB reads JSON natively and can unnest it. BigQuery has a JSON type too.

The other honest use is keeping what an API sent, whole. A raw_payload jsonb column beside the parsed columns means that when the parser turns out to have missed a field, the field is still there.

What you gave up

Every promise the first lesson of this module made, a JSON column breaks.

Nothing checks the keys. Half the shirts say "colour" and half say "color", because two developers wrote two forms, and a query for attrs ->> 'colour' finds half the shirts. A table column cannot be misspelt on insert; a JSON key can, on every insert.

Nothing checks the types. "watts": 65 in one row and "watts": "65W" in the next. A sum over (attrs ->> 'watts')::int fails on the second row, or, if the cast is guarded, silently drops it.

Nothing checks presence. A shirt with no size is a valid document.

The schema has moved into every query. With columns, the database knows the shape of a product and every tool can display it. With JSON, the shape lives in the heads of the people who wrote the inserts, and the first task of anyone new is to discover it:

SELECT key, count(*), count(DISTINCT jsonb_typeof(attrs -> key))
FROM products, jsonb_object_keys(attrs) AS key
GROUP BY key ORDER BY count(*) DESC;

That query lists every key in use, how often, and how many different types it has been given. It is the profiling step from the cleaning module, applied to a column that was supposed to be structured.

Keep the document, promote what you query

The pattern that gets the flexibility without the chaos is to keep the JSON and pull the keys you actually filter and join on into real columns, with real types and real constraints:

ALTER TABLE products
  ADD COLUMN colour text GENERATED ALWAYS AS (attrs ->> 'colour') STORED,
  ADD COLUMN watts  int  GENERATED ALWAYS AS ((attrs ->> 'watts')::int) STORED;
CREATE INDEX ON products (colour);

A generated column is computed from the document on every write, can be indexed like any other column, and fails the insert if the cast fails, which is the type check the JSON did not have. Queries use colour; the document remains for the attributes nobody has needed to promote yet. When a key is queried often enough to matter, it is promoted; the JSON column is where attributes wait to be found important.

Indexing the whole document

Postgres can index a jsonb column with a GIN index, after which attrs @> '{"plug": "type-D"}' uses the index rather than scanning every document. It is the right tool for “find documents containing this fragment” across unknown keys, and it is large and slow to update, which the performance module’s lesson on index types weighs. For a known key, the generated column and its ordinary index are smaller and faster.

The habit

Use JSON for what varies and for what you want to keep whole. The moment a key appears in a WHERE clause, a JOIN or a GROUP BY, give it a column. And run the key-profiling query on any JSON column you inherit, before believing anything about what it contains.