MysqlHow-ToBeginner · 3 min read

How to Use Generated Column for JSON Index in MySQL

In MySQL, you can create a GENERATED column that extracts a value from a JSON column using JSON_EXTRACT(). Then, you can add an INDEX on this generated column to speed up queries filtering by that JSON value.

📐

Syntax

To create a generated column for a JSON value and index it, use this pattern:

json_column: The column storing JSON data.
generated_column: A virtual or stored column extracting a JSON value.
JSON_EXTRACT(json_column, '$.key'): Extracts the value of key from the JSON.
INDEX: Creates an index on the generated column for faster lookups.

sql

ALTER TABLE table_name
ADD COLUMN generated_column VARCHAR(255) AS (JSON_UNQUOTE(JSON_EXTRACT(json_column, '$.key'))) STORED,
ADD INDEX idx_generated_column (generated_column);

💻

Example

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

sql

CREATE TABLE users (
  id INT PRIMARY KEY AUTO_INCREMENT,
  info JSON,
  email VARCHAR(255) AS (JSON_UNQUOTE(JSON_EXTRACT(info, '$.email'))) STORED,
  INDEX idx_email (email)
);

INSERT INTO users (info) VALUES
('{"email": "alice@example.com", "age": 30}'),
('{"email": "bob@example.com", "age": 25}');

SELECT * FROM users WHERE email = 'alice@example.com';

Output

+----+------------------------------------------+---------------------+ | id | info | email | +----+------------------------------------------+---------------------+ | 1 | {"email": "alice@example.com", "age": 30} | alice@example.com | +----+------------------------------------------+---------------------+

⚠️

Common Pitfalls

Common mistakes when using generated columns for JSON indexing include:

Using VIRTUAL instead of STORED for the generated column, which cannot be indexed.
Not unquoting JSON values with JSON_UNQUOTE(), causing index mismatches.
Trying to index complex JSON objects instead of scalar values.

sql

/* Wrong: VIRTUAL generated column cannot be indexed */
ALTER TABLE users
ADD COLUMN email_virtual VARCHAR(255) AS (JSON_EXTRACT(info, '$.email')) VIRTUAL;

CREATE INDEX idx_email_virtual ON users(email_virtual);

/* Right: Use STORED and JSON_UNQUOTE */
ALTER TABLE users
ADD COLUMN email_stored VARCHAR(255) AS (JSON_UNQUOTE(JSON_EXTRACT(info, '$.email'))) STORED,
ADD INDEX idx_email_stored (email_stored);

📊

Quick Reference

Step	Description	Example
1	Create JSON column	info JSON
2	Add STORED generated column extracting JSON key	email VARCHAR(255) AS (JSON_UNQUOTE(JSON_EXTRACT(info, '$.email'))) STORED
3	Add index on generated column	ADD INDEX idx_email (email)
4	Query using generated column	SELECT * FROM users WHERE email = 'alice@example.com';

✅

Key Takeaways

Use STORED generated columns to index JSON-extracted values in MySQL.

Extract scalar JSON values with JSON_UNQUOTE(JSON_EXTRACT(...)) for indexing.

Indexing generated columns speeds up queries filtering by JSON keys.

Avoid using VIRTUAL generated columns if you need an index.

Always test queries to confirm the index is used for better performance.