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...
- Consider the
emp_detailstable 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)- Insert a few dummy records:
mysql> INSERT IGNORE INTO emp_details(emp_no, details) VALUES
('1', '{ "location": "IN", "phone": "+11800000000", "email": "[email protected]", "address": { "line1": "abc", "line2": "xyz street", "city": "Bangalore", "pin": "560103"}}'),
('2', '{ "location": "IN", "phone": "+11800000000", "email...
