Indexing a MySQL JSON Field With a Generated Column

You cannot put an index on a JSON column in MySQL. Try create index on products (attrs) and the server answers that the column supports indexing only via generated columns on a specified JSON path. That sentence is also the solution: pull the field you search out into a generated column, with a real type, and index that. The technique is simple and it has several effects that are not visible in the syntax: a changed collation, a type that rejects bad rows, a possible rewrite of queries you have already written. This article walks through them with the results of a run on 200,000 documents.

The basic recipe

alter table products
  add column sku varchar(20) generated always as (attrs ->> '$.sku') virtual,
  add index idx_sku (sku);

The column is virtual: it stores nothing in the table row and is computed on demand. The index on it does store the values. After this statement:

explain analyze select id from products where sku = 'SKU-123456';

printed Covering index lookup on products using idx_sku (sku='SKU-123456') with an actual time of 0.018 ms. Before the column existed, the same lookup written as attrs ->> '$.sku' = 'SKU-123456' was Table scan on products with rows=200000 and a time of about 70 ms. The index turned a scan into a seek.

Three decisions go into the column definition.

The type. Declare the type you will compare with. A varchar(20) for an identifier, a decimal(8,2) for a price, an int for a count, a datetime for a timestamp stored as an ISO string. The type is what stops '99.99' > '450' style mistakes: with price decimal(8,2) generated always as (cast(attrs ->> '$.price' as decimal(8,2))) and an index, where price between 499.9 and 500 returned its 43 rows in 0.04 ms through a range scan on the index.

Virtual or stored. A stored column is computed at write time and kept in the row. I added one for the SKU: the index came out the same size, 4.5 MB, as the virtual one. The difference is on the table: adding a stored column forced a table copy that took 2.8 seconds on 200,000 rows (algorithm=inplace was refused), and the data grows by the column's width. A virtual column costs nothing in the row and is evaluated when you read it, which is cheap for a lookup of a field. Use stored only if the expression is expensive and read often. Adding the virtual column and its index in one ALTER TABLE, as above, worked; asking for algorithm=instant on that statement was refused with INPLACE ADD or DROP of virtual columns cannot be combined with other ALTER TABLE actions.

The collation. This is the one that bites, and it gets its own section.

The collation changes

JSON strings in MySQL are utf8mb4_bin (case sensitive), as collation(attrs ->> '$.brand') showed. A generated column has the collation of the column, which defaults to the table's, utf8mb4_0900_ai_ci: case and accent insensitive. So the same comparison means different things depending on whether it goes through the JSON or through the column:

where sku = 'sku-123456'                     1 row   (column collation, ai_ci)
where attrs ->> '$.sku' = 'sku-123456'        1 row   (!)

The stored SKU is SKU-123456 in upper case. A comparison on the JSON expression alone is case sensitive, and I had measured attrs ->> '$.brand' = 'acme' returning 0 rows when no generated column covered brand. But once the generated column existed, explain for the second query showed key: idx_sku: the optimizer rewrote the JSON expression to use the generated column, and the comparison changed collation with it. A query that returned 0 rows before returned 1 afterward.

Neither answer is wrong, and the point is that creating an index changed the results of queries that did not mention it. If case sensitivity matters (and for identifiers it often does), say so in the column definition: sku varchar(20) collate utf8mb4_bin generated always as (...). Then both forms agree and the index respects it. Where you want case-insensitive search, declare the collation you want and use the column. Decide on purpose rather than by accident.

The same effect showed up in sorting. order by attrs ->> '$.price' desc limit 3 should sort text, where 99.99 outranks 500.0; after I created the generated decimal price column with an index, explain showed Backward index scan on idx_price and the query returned 500.0, 500.0, 499.99, numeric order. When I wrapped the expression in concat(attrs ->> '$.price', '') so the rewrite could not apply, it sorted as text and returned 99.99 three times. If you create a generated column to fix a slow query, re-run the correctness checks too.

Constraints you get for free

Because the column has a type, the database enforces it on every insert and update, even though the source is a JSON document.

Type errors reject the row. With qty int generated always as (doc ->> '$.qty') virtual:

{"qty": 5}       accepted, qty = 5
{"qty": "7"}     accepted, qty = 7
{"qty": 1.9}     accepted, qty = 2 (rounded)
{"qty": "many"}  ERROR 1366: Incorrect integer value: 'many' for column 'qty' at row 1
{"qty": null}    ERROR 1366: Incorrect integer value: 'null' for column 'qty' at row 1

The last case is a trap. The key is present with a JSON null, ->> turns it into the text null, and the integer conversion fails, so a document that is valid JSON and perfectly reasonable is rejected. A missing qty key is fine and produces a NULL column. If null values are legitimate, define the column with json_value(doc, '$.qty' returning signed) (8.0.21 and later), which returns SQL NULL for a JSON null, or nullif(doc ->> '$.qty', 'null').

Length limits apply. An email longer than varchar(100) raised Data too long for column 'email' when the row was inserted. This is useful as validation and can surprise you in a job that loads documents from elsewhere.

Unique indexes work. A unique index on a generated field enforces uniqueness inside the documents:

create table u1 (
  id int primary key,
  doc json not null,
  email varchar(100) generated always as (doc ->> '$.email') virtual,
  unique key uq_email (email)
);

Inserting a second document with [email protected] failed with Duplicate entry '[email protected]' for key 'u1.uq_email'. Inserting [email protected] failed too, because of the case-insensitive collation, which may be what you want for email. Documents with no email key produced NULL columns, and two of them were accepted, since unique indexes allow multiple NULLs. That is one of the few ways to get a real uniqueness constraint on a value inside JSON.

Functional indexes, the other route

Since 8.0.13 you can index an expression directly, with no named column:

create index idx_price_fn on products ((cast(attrs -> '$.price' as decimal(8,2))));

It is stored as a hidden virtual column. In my run an equality lookup, cast(attrs -> '$.price' as decimal(8,2)) = 499.9, used the index (Index lookup ... idx_price_fn, 4 rows, 0.04 ms). A between 499.9 and 500 on the same expression did not: it was a table scan of about 100 ms, even with force index (idx_price_fn), while the explicit generated column with an ordinary index handled the range in 0.04 ms. I cannot explain that from the plan, only report it for this version and setup, and so for ranges I use a named generated column and test the plan. A functional index also has no name to put in a select list or a view, which is the practical reason to prefer the column: you can write where price > 100 and read the query.

A multi-valued functional index is the way to index JSON arrays (cast(attrs -> '$.tags' as char(10) array)), used by MEMBER OF; the search side is in searching values inside a MySQL JSON array.

Sizes and cost

The indexes from this article, as reported by mysql.innodb_index_stats, on a primary key of 50.6 MB:

idx_sku (virtual column)           4.5 MB
idx_sku_s (stored column)          4.5 MB
idx_price (decimal column)         5.5 MB
idx_price_fn (functional)          5.5 MB
idx_tags (multi-valued, 2 tags)    6.5 MB

Each is a tenth of the table or less. Every index also adds a cost to every write that touches the field: the document must be parsed to compute the key. If you update the JSON key often, each update touches the index too, and the partial-update savings described in updating parts of a MySQL JSON document in place no longer apply to that key.

Choosing

For a field you search by equality or range: a virtual generated column with a typed, collated definition and an ordinary index, and read it as a column. For a one-off index on an expression you will not name: a functional index, with an equality test. For an array: a multi-valued index. For a field that is also a join key or the basis of every report: leave the document and make it a real column, as discussed in the guide and, for the migration steps, in when JSONB is the wrong choice, which applies equally to MySQL. The JSON formatter can help you see the structure of a document before choosing the path.

When a generated column is not worth it

The field is rarely queried. A scan of 200,000 small documents took 70 ms. If it runs once a day, do not index it.

The table is small. Under a few thousand rows the optimizer will scan anyway.

You need a different collation for different queries. A column has one. Index two columns, or keep the field as text with the collation you need.

The key is untrusted. Generated columns reject bad values with an error on insert, which converts a data quality problem into a failed write. If the JSON arrives from a third party and must be accepted regardless, extract with json_value(... null on error) or leave the field unindexed.

You are on MariaDB. Virtual columns and indexes work there too, but the JSON is text, so extraction costs more, and the ->> operator does not exist (MySQL JSON versus MariaDB JSON).

The manual covers generated columns, secondary indexes on virtual columns and functional key parts.