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.
- 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)
- 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...