Indexing a JSON Path Without Changing the Document
An e-commerce catalog service stores product attributes in a JSON column. The product_catalog table has 3.1 million rows and is defined as CREATE TABLE product_catalog (id INT PRIMARY KEY, sku VARCHAR(32), attrs JSON, updated_at DATETIME);. A frequent query filters by brand: SELECT id, sku FROM product_catalog WHERE JSON_UNQUOTE(JSON_EXTRACT(attrs, '$.brand')) = 'Acme';. It takes 4.2 seconds, and EXPLAIN shows a full table scan. The team tried ALTER TABLE product_catalog ADD INDEX idx_attrs (attrs);, which failed with error 3152. About 8% of rows have no brand key. The application also runs a nightly load that inserts 200,000 rows and cannot be blocked for long. The DBA wants a schema change that makes the brand filter use an index without changing the application's JSON document format.
1. The attempted `CREATE INDEX idx_attrs ON product_catalog(attrs);` failed with error 3152. Explain why, and name the two MySQL 8.0 mechanisms that can index this path expression.
Read the worked answer
MySQL does not permit a normal secondary index on a JSON column. Error 3152 means the JSON column can be indexed only through a generated column that extracts a scalar value, or through a functional index. A B-tree on the whole JSON document would not help anyway: the predicate is on $.brand, and the optimizer cannot use a whole-document index to resolve a path comparison.
The two mechanisms are:
- A stored or virtual generated column with an index on that column.
- A functional index, introduced in MySQL 8.0.13, whose indexed expression matches the predicate expression.
In both cases the indexed value must be scalar, and the path must be a constant string. The application's JSON format does not need to change; the schema adds a derived, indexable projection of attrs.
2. Write the DDL for a generated-column solution and for a functional-index solution. Show the query form that lets the optimizer use each index.
Read the worked answer
Generated column:
ALTER TABLE product_catalog
ADD COLUMN brand VARCHAR(64)
GENERATED ALWAYS AS (JSON_UNQUOTE(JSON_EXTRACT(attrs, '$.brand'))) STORED,
ADD INDEX idx_brand (brand);
Query using the generated column:
SELECT id, sku
FROM product_catalog
WHERE brand = 'Acme';
MySQL 8.0 can also match the original expression to the generated column, but querying the column directly is clearer and avoids expression-normalization surprises.
Functional index:
CREATE INDEX idx_brand_func
ON product_catalog ((JSON_UNQUOTE(JSON_EXTRACT(attrs, '$.brand'))));
With a functional index, the query must use the same expression that was indexed:
SELECT id, sku
FROM product_catalog
WHERE JSON_UNQUOTE(JSON_EXTRACT(attrs, '$.brand')) = 'Acme';
The shorthand attrs->>'$.brand' is normally normalized to the same expression, but verify with EXPLAIN rather than assuming. The key point is that the index is on the expression's result, not on the JSON document.
3. The brand key is missing on 8% of rows, and the nightly load inserts 200,000 rows. What should the DBA consider before and after adding the index?
Read the worked answer
Missing keys produce NULL from JSON_EXTRACT and JSON_UNQUOTE, so the generated column or functional index stores NULL for those rows. That is acceptable, but the query brand = 'Acme' will not match them, and any reporting that must include products without a brand needs brand IS NULL or a separate path.
The column type and collation matter. If the application compares brands case-insensitively, define the generated column with the expected collation; otherwise index lookups may not match application behavior. A VARCHAR(64) is only safe if brand values cannot exceed 64 characters; otherwise truncation or errors can occur depending on SQL mode.
For the nightly load, adding a stored generated column rebuilds the table. MySQL 8.0 can do this in place with concurrent DML using ALGORITHM=INPLACE, LOCK=NONE, but it still performs a rebuild and consumes I/O. Test the DDL on a replica, monitor replication lag, and schedule it outside the load window. Virtual generated columns avoid storing the value but still need an index; the index itself is the persistent structure.