How to Create Index on JSON Column in MySQL

In MySQL, you cannot directly create an index on a JSON column. Instead, create a GENERATED column extracting the JSON value you want to index, then create an index on that generated column.

Syntax

To index a JSON column, first add a GENERATED column that extracts a specific JSON value using JSON_UNQUOTE(JSON_EXTRACT()) or the arrow operator ->>. Then create an index on this generated column.

Example syntax:

ALTER TABLE table_name
ADD COLUMN generated_col VARCHAR(255) AS (json_col->>'$.key') STORED;
CREATE INDEX idx_generated_col ON table_name (generated_col);

Here:

• generated_col is the new column storing the extracted JSON value.
• json_col->>'$.key' extracts the JSON key's value as text.
• STORED means the generated column saves data physically, allowing indexing.
• CREATE INDEX creates the index on the generated column.

sql

ALTER TABLE table_name
ADD COLUMN generated_col VARCHAR(255) AS (json_col->>'$.key') STORED;
CREATE INDEX idx_generated_col ON table_name (generated_col);

Example

This example shows how to create a table with a JSON column, add a generated column extracting a JSON key, and create an index on it.

sql

CREATE TABLE products (
  id INT PRIMARY KEY AUTO_INCREMENT,
  info JSON,
  product_name VARCHAR(100) AS (info->>'$.name') STORED
);

CREATE INDEX idx_product_name ON products (product_name);

INSERT INTO products (info) VALUES
('{"name": "Pen", "price": 1.5}'),
('{"name": "Notebook", "price": 3.0}');

SELECT * FROM products WHERE product_name = 'Pen';

Output

+----+-----------------------------+--------------+
| id | info                        | product_name |
+----+-----------------------------+--------------+
|  1 | {"name": "Pen", "price": 1.5} | Pen          |
+----+-----------------------------+--------------+

Common Pitfalls

• Trying to create an index directly on a JSON column will fail because MySQL does not support indexing JSON types directly.
• Using VIRTUAL generated columns instead of STORED will not allow indexing.
• Extracting complex JSON structures instead of scalar values will cause errors or inefficient indexes.

sql

/* Wrong: Trying to index JSON column directly */
CREATE INDEX idx_info ON products (info); -- This will cause an error

/* Wrong: Using VIRTUAL generated column */
ALTER TABLE products
ADD COLUMN product_name VARCHAR(100) AS (info->>'$.name') VIRTUAL;
CREATE INDEX idx_product_name ON products (product_name); -- Error: Can't index virtual column

/* Right: Use STORED generated column */
ALTER TABLE products
ADD COLUMN product_name VARCHAR(100) AS (info->>'$.name') STORED;
CREATE INDEX idx_product_name ON products (product_name);

Quick Reference

Step	Description	Example
1	Add a STORED generated column extracting JSON key	ALTER TABLE t ADD COLUMN col VARCHAR(255) AS (json_col->>'$.key') STORED;
2	Create an index on the generated column	CREATE INDEX idx_col ON t (col);
3	Query using the generated column for fast lookups	SELECT * FROM t WHERE col = 'value';

Key Takeaways

MySQL does not support direct indexing on JSON columns.

Use STORED generated columns to extract JSON values for indexing.

Create indexes on these generated columns to speed up JSON queries.

Avoid VIRTUAL generated columns for indexing as they are not allowed.

Extract scalar JSON values, not complex objects, for indexing.