Indexing for JSON using generated columns

JSON columns cannot be indexed directly. So if you want to use an index on a JSON column, you can extract the information using virtual columns and a created index on the virtual column.

How to do it...

1. Consider the emp_details table that you created in Chapter 3, Using MySQL (Advanced), Using JSON section:

mysql> SHOW CREATE TABLE emp_details\G
*************************** 1. row ***************************
       Table: emp_details
Create Table: CREATE TABLE `emp_details` (
  `emp_no` int(11) NOT NULL,
  `details` json DEFAULT NULL,
  PRIMARY KEY (`emp_no`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)

2. Insert a few dummy records:

mysql> INSERT IGNORE INTO...