Indexing JSON via Virtual Generated Columns0%

Indexing JSON via Virtual Generated Columns

Advanced14 min read•Updated: 2026-09-12

Indexing JSON via Virtual Generated Columns

Because JSON documents contain flexible, arbitrary schema structures, MySQL cannot directly create a standard B+Tree index on an entire JSON column.

If a query filters with WHERE attributes->>'$.brand' = 'Alienware', MySQL must perform a Full Table Scan (type: ALL), inspecting and parsing the JSON of every row.

To index JSON attributes with high-performance B+Tree lookups, MySQL provides Virtual Generated Columns (and Multi-Valued Indexes).

Step 1: Understanding Generated Columns

A Generated Column is a column whose value is calculated automatically from an expression:

VIRTUAL (Default): Column values are calculated on-the-fly when read. They consume zero disk space.
STORED: Column values are calculated and written to disk when rows are inserted or updated.

The Architectural Miracle: Even though a VIRTUAL generated column consumes zero table disk space, MySQL allows you to create a standard B+Tree index on it! The index physically stores the extracted keys, enabling $O(log N)$ point lookups!

Step 2: Creating a Virtual Generated Column & Index

1-- 1. Add virtual column extracting 'brand' from JSON
2ALTER TABLE product_catalogs
3ADD COLUMN brand VARCHAR(50) 
4GENERATED ALWAYS AS (attributes->>'$.brand') VIRTUAL;
5 
6-- 2. Add high-speed B+Tree index on the virtual column!
7CREATE INDEX idx_products_brand ON product_catalogs (brand);

Step 3: Automatic Query Optimizer Rewrite

Now, when an application queries the table using either the generated column OR the original JSON path syntax:

1-- Query using virtual column:
2EXPLAIN SELECT name, price FROM product_catalogs WHERE brand = 'Alienware';
3 
4-- Query using raw JSON path expression:
5EXPLAIN SELECT name, price FROM product_catalogs WHERE attributes->>'$.brand' = 'Alienware';

`EXPLAIN` Result:

type: ref
key: idx_products_brand
rows: 1

The MySQL query optimizer automatically recognizes that attributes->>'$.brand' matches the virtual column expression and transparently redirects execution to the B+Tree index!

Multi-Valued Indexes for JSON Arrays (MySQL 8.0.17+)

If your JSON document contains an array (e.g., tags: ["gaming", "vr", "portable"]), how do you index individual elements?

MySQL 8.0.17+ introduces Multi-Valued Indexes:

1-- Create an index directly over an array of strings
2CREATE INDEX idx_json_tags 
3ON product_catalogs ((CAST(attributes->'$.tags' AS CHAR(30) ARRAY)));
4 
5-- Query using MEMBER OF() syntax
6SELECT name FROM product_catalogs 
7WHERE 'gaming' MEMBER OF(attributes->'$.tags');

This query performs an index seek against idx_json_tags with zero table scans!

Interactive Quiz• Knowledge Check

Multiple Choice Questions

Question 1

Why can't MySQL create a standard B+Tree index directly on a raw JSON column?

Question 2

How much disk storage space does a VIRTUAL generated column consume in table data pages?

Question 3

Can a B+Tree index be created on a VIRTUAL generated column?

Question 4

What does the MySQL optimizer do when a query filters using raw JSON syntax matching a virtual indexed column?

Question 5

Which MySQL 8.0.17+ feature enables direct indexing of JSON array elements?

Next Lesson

Advanced Analytical JSON Queries

Continue learning with hands-on practice, examples, and exercises in the upcoming topic.

Related Lessons

Previous Lesson	Next Lesson
Modifying JSON Documents (JSON_SET, JSON_INSERT, JSON_REMOVE)	Advanced Analytical JSON Queries

Practice Quiz

Test your understanding of this lesson with 5 questions. Each question has one correct answer.

Previous Next

PreviousPrevNext

MSK Institute • Shikohabad

Loading Page Content...

Preparing courses, live labs, and learning resources.सामग्री लोड हो रही है, कृपया प्रतीक्षा करें...

Connecting serverPlease wait...

💻 Practical Labs🚀 Free Demo📜 Verified Certificates

Advanced Analytical JSON Queries

Indexing JSON via Virtual Generated Columns

Advanced14 min read•Updated: 2026-09-12

Study Materials Cheatsheet

Indexing JSON via Virtual Generated Columns

Because JSON documents contain flexible, arbitrary schema structures, MySQL cannot directly create a standard B+Tree index on an entire JSON column.

If a query filters with WHERE attributes->>'$.brand' = 'Alienware', MySQL must perform a Full Table Scan (type: ALL), inspecting and parsing the JSON of every row.

To index JSON attributes with high-performance B+Tree lookups, MySQL provides Virtual Generated Columns (and Multi-Valued Indexes).

Step 1: Understanding Generated Columns

A Generated Column is a column whose value is calculated automatically from an expression:

VIRTUAL (Default): Column values are calculated on-the-fly when read. They consume zero disk space.
STORED: Column values are calculated and written to disk when rows are inserted or updated.

Step 2: Creating a Virtual Generated Column & Index

1-- 1. Add virtual column extracting 'brand' from JSON
2ALTER TABLE product_catalogs
3ADD COLUMN brand VARCHAR(50) 
4GENERATED ALWAYS AS (attributes->>'$.brand') VIRTUAL;
5 
6-- 2. Add high-speed B+Tree index on the virtual column!
7CREATE INDEX idx_products_brand ON product_catalogs (brand);

Step 3: Automatic Query Optimizer Rewrite

Now, when an application queries the table using either the generated column OR the original JSON path syntax:

1-- Query using virtual column:
2EXPLAIN SELECT name, price FROM product_catalogs WHERE brand = 'Alienware';
3 
4-- Query using raw JSON path expression:
5EXPLAIN SELECT name, price FROM product_catalogs WHERE attributes->>'$.brand' = 'Alienware';

`EXPLAIN` Result:

type: ref
key: idx_products_brand
rows: 1

The MySQL query optimizer automatically recognizes that attributes->>'$.brand' matches the virtual column expression and transparently redirects execution to the B+Tree index!

Multi-Valued Indexes for JSON Arrays (MySQL 8.0.17+)

If your JSON document contains an array (e.g., tags: ["gaming", "vr", "portable"]), how do you index individual elements?

MySQL 8.0.17+ introduces Multi-Valued Indexes:

1-- Create an index directly over an array of strings
2CREATE INDEX idx_json_tags 
3ON product_catalogs ((CAST(attributes->'$.tags' AS CHAR(30) ARRAY)));
4 
5-- Query using MEMBER OF() syntax
6SELECT name FROM product_catalogs 
7WHERE 'gaming' MEMBER OF(attributes->'$.tags');

This query performs an index seek against idx_json_tags with zero table scans!

Interactive Quiz• Knowledge Check

Multiple Choice Questions

Question 1

Why can't MySQL create a standard B+Tree index directly on a raw JSON column?

Question 2

How much disk storage space does a VIRTUAL generated column consume in table data pages?

Question 3

Can a B+Tree index be created on a VIRTUAL generated column?

Question 4

What does the MySQL optimizer do when a query filters using raw JSON syntax matching a virtual indexed column?

Question 5

Which MySQL 8.0.17+ feature enables direct indexing of JSON array elements?

Next Lesson

Advanced Analytical JSON Queries

Continue learning with hands-on practice, examples, and exercises in the upcoming topic.

Related Lessons

Previous Lesson	Next Lesson
Modifying JSON Documents (JSON_SET, JSON_INSERT, JSON_REMOVE)	Advanced Analytical JSON Queries

Practice Quiz

Test your understanding of this lesson with 5 questions. Each question has one correct answer.

Previous Next

Indexing JSON via Virtual Generated Columns

Step 1: Understanding Generated Columns

Step 2: Creating a Virtual Generated Column & Index

Step 3: Automatic Query Optimizer Rewrite

EXPLAIN Result:

Multi-Valued Indexes for JSON Arrays (MySQL 8.0.17+)

Multiple Choice Questions

Advanced Analytical JSON Queries

Related Lessons

Practice Quiz

Loading Page Content...

Indexing JSON via Virtual Generated Columns

Step 1: Understanding Generated Columns

Step 2: Creating a Virtual Generated Column & Index

Step 3: Automatic Query Optimizer Rewrite

EXPLAIN Result:

Multi-Valued Indexes for JSON Arrays (MySQL 8.0.17+)

Multiple Choice Questions

Advanced Analytical JSON Queries

Related Lessons

Practice Quiz

`EXPLAIN` Result:

`EXPLAIN` Result: