MySQL JSON Index: Generated Columns and Functional Indexes

A JSON column cannot be indexed as a whole. A query such as WHERE attributes->>'$.color' = 'blue' reads every row and parses its JSON. To make such searches fast, you index the values inside the documents. MySQL gives you three ways.

query.sql

SQL

SET SESSION cte_max_recursion_depth = 100000;

CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(40) NOT NULL,
    attributes JSON NOT NULL
);
INSERT INTO products (name, attributes)
WITH RECURSIVE n AS (SELECT 1 AS i UNION ALL SELECT i + 1 FROM n WHERE i < 20000)
SELECT CONCAT('Product ', i),
       JSON_OBJECT('color', ELT(1 + MOD(i, 5), 'red', 'blue', 'green', 'black', 'white'),
                   'weight_g', 100 + MOD(i * 7, 900),
                   'tags', JSON_ARRAY(CONCAT('tag', MOD(i, 50)), CONCAT('group', MOD(i, 7))))
FROM n;
ANALYZE TABLE products;

EXPLAIN FORMAT=TRADITIONAL SELECT id FROM products WHERE attributes->>'$.color' = 'blue';

Output

OUTPUT

+----------------+---------+----------+----------+
| Table          | Op      | Msg_type | Msg_text |
+----------------+---------+----------+----------+
| learn.products | analyze | status   | OK       |
+----------------+---------+----------+----------+
1 row in set

+----+-------------+----------+------------+------+---------------+------+---------+------+-------+----------+-------------+
| id | select_type | table    | partitions | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra       |
+----+-------------+----------+------------+------+---------------+------+---------+------+-------+----------+-------------+
|  1 | SIMPLE      | products | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 19955 |   100.00 | Using where |
+----+-------------+----------+------------+------+---------------+------+---------+------+-------+----------+-------------+
1 row in set

type: ALL: a full scan of 20,000 rows, parsing JSON in each.

Way 1: a generated column with an index

Create a virtual generated column that extracts the value, then index it (Generated Columns):

query.sql

SQL

ALTER TABLE products
    ADD COLUMN color VARCHAR(20) GENERATED ALWAYS AS (attributes->>'$.color') VIRTUAL,
    ADD INDEX idx_color (color);
ANALYZE TABLE products;

EXPLAIN FORMAT=TRADITIONAL SELECT id FROM products WHERE color = 'blue';
EXPLAIN FORMAT=TRADITIONAL SELECT id FROM products WHERE attributes->>'$.color' = 'blue';

Output

OUTPUT

+----------------+---------+----------+----------+
| Table          | Op      | Msg_type | Msg_text |
+----------------+---------+----------+----------+
| learn.products | analyze | status   | OK       |
+----------------+---------+----------+----------+
1 row in set

+----+-------------+----------+------------+------+---------------+-----------+---------+-------+------+----------+-------------+
| id | select_type | table    | partitions | type | possible_keys | key       | key_len | ref   | rows | filtered | Extra       |
+----+-------------+----------+------------+------+---------------+-----------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | products | NULL       | ref  | idx_color     | idx_color | 83      | const | 4000 |   100.00 | Using index |
+----+-------------+----------+------------+------+---------------+-----------+---------+-------+------+----------+-------------+
1 row in set

+----+-------------+----------+------------+------+---------------+-----------+---------+-------+------+----------+-------+
| id | select_type | table    | partitions | type | possible_keys | key       | key_len | ref   | rows | filtered | Extra |
+----+-------------+----------+------------+------+---------------+-----------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | products | NULL       | ref  | idx_color     | idx_color | 83      | const | 4000 |   100.00 | NULL  |
+----+-------------+----------+------------+------+---------------+-----------+---------+-------+------+----------+-------+
1 row in set

The first query uses idx_color. The second query, which still uses the JSON expression, also uses the index, because MySQL recognises that the expression matches the generated column’s expression. You also get a normal column color that you can select, group by and use in a WHERE.

Way 2: a functional index

A functional index on the same expression, without a visible extra column (MySQL 8.0.13 and later):

query.sql

SQL

1
2
3

ALTER TABLE products ADD INDEX idx_weight ((CAST(attributes->>'$.weight_g' AS UNSIGNED)));
ANALYZE TABLE products;
EXPLAIN FORMAT=TRADITIONAL SELECT id FROM products WHERE CAST(attributes->>'$.weight_g' AS UNSIGNED) BETWEEN 100 AND 120;

Output

OUTPUT

+----------------+---------+----------+----------+
| Table          | Op      | Msg_type | Msg_text |
+----------------+---------+----------+----------+
| learn.products | analyze | status   | OK       |
+----------------+---------+----------+----------+
1 row in set

+----+-------------+----------+------------+-------+---------------+------------+---------+------+------+----------+-------------+
| id | select_type | table    | partitions | type  | possible_keys | key        | key_len | ref  | rows | filtered | Extra       |
+----+-------------+----------+------------+-------+---------------+------------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | products | NULL       | range | idx_weight    | idx_weight | 9       | NULL |  467 |   100.00 | Using where |
+----+-------------+----------+------------+-------+---------------+------------+---------+------+------+----------+-------------+
1 row in set

Your query must use the same expression, including the same CAST. A cast to the type you want is needed, because ->> returns text, and a numeric range on text would compare as strings. JSON_VALUE(attributes, '$.weight_g' RETURNING UNSIGNED) is an equivalent, cleaner form to write in both the index and the query.

Way 3: a multi-valued index (for arrays)

The tags value is an array. You want “products tagged tag7“. A multi-valued index (MySQL 8.0.17 and later) stores one index entry per array element:

query.sql

SQL

ALTER TABLE products ADD INDEX idx_tags ((CAST(attributes->'$.tags' AS CHAR(10) ARRAY)));
ANALYZE TABLE products;

EXPLAIN FORMAT=TRADITIONAL SELECT id FROM products WHERE 'tag7' MEMBER OF (attributes->'$.tags');
EXPLAIN FORMAT=TRADITIONAL SELECT id FROM products WHERE JSON_CONTAINS(attributes->'$.tags', '["tag7", "group0"]');
EXPLAIN FORMAT=TRADITIONAL SELECT id FROM products WHERE JSON_OVERLAPS(attributes->'$.tags', '["tag7", "tag8"]');

Output

OUTPUT

+----------------+---------+----------+----------+
| Table          | Op      | Msg_type | Msg_text |
+----------------+---------+----------+----------+
| learn.products | analyze | status   | OK       |
+----------------+---------+----------+----------+
1 row in set

+----+-------------+----------+------------+------+---------------+----------+---------+-------+------+----------+-------------+
| id | select_type | table    | partitions | type | possible_keys | key      | key_len | ref   | rows | filtered | Extra       |
+----+-------------+----------+------------+------+---------------+----------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | products | NULL       | ref  | idx_tags      | idx_tags | 43      | const |  400 |   100.00 | Using where |
+----+-------------+----------+------------+------+---------------+----------+---------+-------+------+----------+-------------+
1 row in set

+----+-------------+----------+------------+-------+---------------+----------+---------+------+------+----------+-------------+
| id | select_type | table    | partitions | type  | possible_keys | key      | key_len | ref  | rows | filtered | Extra       |
+----+-------------+----------+------------+-------+---------------+----------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | products | NULL       | range | idx_tags      | idx_tags | 43      | NULL | 3257 |   100.00 | Using where |
+----+-------------+----------+------------+-------+---------------+----------+---------+------+------+----------+-------------+
1 row in set

+----+-------------+----------+------------+-------+---------------+----------+---------+------+------+----------+-------------+
| id | select_type | table    | partitions | type  | possible_keys | key      | key_len | ref  | rows | filtered | Extra       |
+----+-------------+----------+------------+-------+---------------+----------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | products | NULL       | range | idx_tags      | idx_tags | 43      | NULL |  800 |   100.00 | Using where |
+----+-------------+----------+------------+-------+---------------+----------+---------+------+------+----------+-------------+
1 row in set

All three conditions can use idx_tags, as long as they match a small share of the rows (here about 2 to 3 percent for tag7). If a condition matches a large part of the table, the optimizer may prefer a full scan even when the index is available, as it does for any index (Selectivity):

Condition	Meaning
`value MEMBER OF (path)`	The array contains the value
`JSON_CONTAINS(path, '[a, b]')`	The array contains all the listed values
`JSON_OVERLAPS(path, '[a, b]')`	The array contains at least one of them

query.sql

SQL

SELECT COUNT(*) AS tag7_products FROM products WHERE 'tag7' MEMBER OF (attributes->'$.tags');

Output

OUTPUT

+---------------+
| tag7_products |
+---------------+
|           400 |
+---------------+
1 row in set

The syntax CAST(... AS CHAR(10) ARRAY) declares the element type. Other types: UNSIGNED, SIGNED, DATE, DATETIME, DECIMAL, BINARY. (For CHAR, use a fixed maximum length that covers your values. A longer value gives an error on insert.)

Limits of multi-valued indexes

At most one multi-valued key part per index.
The index entry count is the sum of the elements: a huge array means a huge index (the maximum is 10,000 values per record).
They are not allowed for primary keys.
They cannot be used for sorting, range scans, or as a covering index.

Choosing the way

Need	Choice
A scalar value you filter or sort on often, and also want to select as a column	Generated column + index
A scalar value, only used in the `WHERE`	Functional index
“Does the array contain…” searches	Multi-valued index
Searching text inside JSON	`FULLTEXT` on a generated column holding the text

Which expression does the optimizer match?

The query expression and the index expression must be the same after MySQL normalises them. These are not the same:

attributes->>'$.color' and JSON_UNQUOTE(JSON_EXTRACT(attributes, '$.color')) normalise to the same thing, and match.
attributes->'$.color' = '"blue"' compares JSON to JSON and does not match an index on the unquoted text.
CAST(attributes->>'$.weight_g' AS UNSIGNED) and attributes->>'$.weight_g' are different expressions.

Test with EXPLAIN every time.

Cost

Each index adds disk space and slows writes: updating a JSON document must update the index entries of every index that depends on it. Index only the paths that your queries use.

lightbulb

The design hint

If you are building several JSON indexes on the same paths, those values probably want to be normal columns. Indexes on JSON are a good answer for a few flexible attributes, not for the core of your data model (JSON vs Columns).