The first time I saw a MySQL JSON column melt a production instance, the table was only twelve million rows. A product team had shipped a flexible attributes column, the API filtered on one key inside it, and every request was a full table scan. The CPU graph looked like a step function: flat, deploy, vertical.
MySQL's JSON type is genuinely good. Documents are validated on write, stored in a binary format with an internal dictionary, and partial in-place updates exist for some operations. What the JSON type does not give you is indexing. You cannot build an index directly on a JSON column, and the optimizer will not magically find rows by a path inside the document. Everything indexable has to be extracted first.
These are my field notes on the three extraction mechanisms that actually work in 8.0 and 8.4 — generated columns, functional indexes, and multi-valued indexes — plus the pruning rules and the point at which I stop indexing JSON and promote the field to a real column.
A JSON column is invisible to the optimizer
Start with the failure mode. A predicate like a JSON_EXTRACT comparison over a plain JSON column gives the optimizer nothing to work with. There is no index on the extracted value, no histogram on it, and no way to estimate selectivity beyond a guess. You get a full scan, and the per-row cost is worse than a normal scan because each row has to be parsed far enough to evaluate the path.
EXPLAIN makes this obvious if you look: type ALL, no possible_keys, and a filtered estimate that is usually fiction. The fix is never a server knob. The fix is to materialize the path you filter on into something InnoDB can index.
This is one of the places where MySQL and PostgreSQL diverge most sharply — PostgreSQL's jsonb has GIN indexes that cover arbitrary containment queries out of the box, while MySQL asks you to declare each indexed path up front. I wrote up the broader comparison in MySQL JSON vs PostgreSQL JSONB; the short version is that MySQL's approach costs more design effort but produces ordinary B-tree indexes with predictable behavior.
Generated columns turn JSON paths into indexable columns
The classic pattern is a stored or virtual generated column that extracts one scalar path, with a regular secondary index on top. I default to VIRTUAL: the value is computed when read and when the index is maintained, so you pay no extra row storage, and adding the column is an instant metadata change.
ALTER TABLE orders
ADD COLUMN customer_tier VARCHAR(20)
GENERATED ALWAYS AS (
JSON_UNQUOTE(JSON_EXTRACT(attributes, '$.customer.tier'))
) VIRTUAL,
ADD INDEX idx_customer_tier (customer_tier);
-- The optimizer rewrites matching expressions to use the column:
EXPLAIN SELECT id, total
FROM orders
WHERE JSON_UNQUOTE(JSON_EXTRACT(attributes, '$.customer.tier')) = 'enterprise';The detail that saves you a rewrite of every query: the optimizer performs expression matching. If the WHERE clause contains an expression identical to the generated column definition, MySQL substitutes the column and uses the index, even though the query never names it. Identical is doing real work in that sentence — the column-reference arrow syntax and the JSON_EXTRACT spelling are treated as equivalent, but a different path string, a missing JSON_UNQUOTE, or a wrapping function breaks the match and you are back to a scan.
Use STORED instead of VIRTUAL when the extraction is expensive and read frequently outside the index, or when you need the column in a primary key. Otherwise VIRTUAL plus an index gives you the materialization only where it matters: inside the B-tree.
Functional indexes: the same machinery, less ceremony
Since 8.0.13 you can skip declaring the column and index the expression directly. Under the hood MySQL creates a hidden virtual generated column anyway, so the semantics and the expression-matching rules are the same.
CREATE INDEX idx_orders_region ON orders (
(CAST(attributes->>'$.region' AS CHAR(16)) COLLATE utf8mb4_bin)
);Note the CAST and the explicit collation. JSON_UNQUOTE and the unquoting extraction operator return strings with a specific collation (utf8mb4_bin in current releases), and a functional index over a JSON extraction needs a deterministic type to be usable. If the comparison in your query resolves to a different collation than the indexed expression, the index is silently skipped. Collation mismatches are a recurring index killer well beyond JSON — the same trap shows up in ordinary JOINs, which I covered in the utf8mb4 collation notes.
My rule: functional indexes for one-off paths that only ever appear in WHERE clauses, named generated columns for anything the application also selects, groups by, or joins on. A named column is greppable; a hidden one is a surprise for the next person.
Multi-valued indexes for arrays (8.0.17+)
Scalar extraction fails the moment the JSON path is an array. A document with a tags array cannot be flattened into one scalar column — one row needs multiple index entries. That is what multi-valued indexes do: since 8.0.17, CAST with the ARRAY keyword inside an index definition builds an InnoDB index with one entry per array element, all pointing at the same row.
CREATE TABLE devices (
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
profile JSON NOT NULL,
INDEX idx_fw_targets (
(CAST(profile->'$.firmware_targets' AS UNSIGNED ARRAY))
)
);
-- All three predicate forms can use the multi-valued index:
SELECT id FROM devices
WHERE 1809 MEMBER OF (profile->'$.firmware_targets');
SELECT id FROM devices
WHERE JSON_CONTAINS(profile->'$.firmware_targets', CAST('[1809, 2004]' AS JSON));
SELECT id FROM devices
WHERE JSON_OVERLAPS(profile->'$.firmware_targets', CAST('[1809, 2004]' AS JSON));The semantics differ and the difference matters. MEMBER OF matches rows whose array contains one value. JSON_CONTAINS requires every element of the argument to be present — an AND across values. JSON_OVERLAPS matches if any element is shared — an OR. In EXPLAIN, a multi-valued index lookup shows up as a range scan with the index in possible_keys; if you see ALL, the optimizer rejected it, usually because the CAST target type in the query does not match the one in the index definition.
Operational caveats I have hit: multi-valued indexes cannot be covering indexes, cannot be used for ORDER BY, and each array element is a separate index record, so a hot table with fifty-element arrays pays roughly fifty index maintenance operations per write. Watch the write amplification before you index a large array on a high-churn table.
The traps: type coercion and silent index skips
Every JSON indexing incident I have debugged reduces to one of three coercion problems.
- Quoted versus unquoted strings. JSON_EXTRACT returns a JSON value; comparing it to a plain string compares apples to serialized apples. Index the JSON_UNQUOTE form or the unquoting operator, and use the same form in queries.
- Numbers stored as JSON strings. A document with a quoted numeric value will not match an UNSIGNED ARRAY cast or a numeric MEMBER OF probe. Fix the writers, or index as CHAR ARRAY and accept string ordering.
- Collation drift. The extraction functions return utf8mb4_bin strings. Comparing against a column or literal in a case-insensitive collation can either bypass the index or change matching semantics. Pin the collation on the indexed expression and be explicit in queries.
The diagnostic loop is always the same: run EXPLAIN, check whether the index appears, and if it does not, diff the query expression against the index definition character by character. The optimizer's matching is textual-structural, not semantic, and it forgives almost nothing.
When to promote a JSON field to a real column
Generated columns are a bridge, not a destination. My promotion checklist, learned the slow way:
- The field appears in WHERE clauses of more than a couple of query families, or in JOIN conditions at all. Joining through JSON extractions is where plans go to die.
- You need a foreign key, a uniqueness guarantee the application actually relies on, or a NOT NULL constraint with a default.
- You want optimizer statistics. Histograms and index dives work on real columns; selectivity estimation over generated extractions is weaker, and bad estimates cascade into bad join orders.
- The field has stopped changing shape. If the schema of that key has been stable for six months, it is not flexible data anymore — it is a column you are storing expensively.
The promotion itself is a normal online schema change: add the real column, backfill from the JSON, dual-write during the transition, then drop the generated column. On large tables I run the backfill through an online schema change tool rather than a single UPDATE; the tradeoffs between the two main tools are in my gh-ost vs pt-osc field notes.
Watching JSON query health over time — and where MonPG fits
The uncomfortable part of JSON indexing is that regressions are silent. Nobody gets an error when an index stops matching after an innocent-looking query refactor; the query just quietly becomes a scan, and you find out from the latency graph. The signals worth tracking are the ones that catch it early: rows examined per query family, the ratio of examined to returned rows, and full-scan counts on tables that carry JSON indexes.
Full disclosure on where my own product stands: MonPG is a PostgreSQL monitoring platform, and MySQL support is currently in development — it does not monitor MySQL today. We are building exactly this kind of query-family evidence for MySQL right now, and you can follow progress or leave your use case on the MySQL monitoring (coming soon) page. If your fleet also includes PostgreSQL, the platform is live for that side and you can start there — the jsonb equivalents of every problem in this post are already first-class citizens.