Indexing MySQL JSON

When migrating a database, it is vital to implement all its features available in the source DBMS. For example, MySQL supports JSON type starting from version 5.7.8, but it does not allow to index JSON values directly.

The workaround for indexing JSON in MySQL is to use generated column that extracts a scalar value from the JSON column. Assume, we have table "employees" defined as follows:

CREATE TABLE `employees` (  
    `id` INT UNSIGNED NOT NULL,
    `fullname` JSON NOT NULL,
    `dept_id` INT, 
    `hire_date` DATE, 
    PRIMARY KEY (`id`)
);

Each value in JSON column "fullname" includes "firstname" and "lastname" components:

{"firstname": "Fred", "lastname": "Astaire"}
{"firstname": "Bill", "lastname": "Robinson"}
{"firstname": "Don", "lastname": "Rickles"}
... 

Now we want to index that JSON column on "lastname" component of the data. For this purpose we need to modify the CREATE TABLE statement as:

CREATE TABLE `employees` (  
    `id` INT UNSIGNED NOT NULL,
    `fullname` JSON NOT NULL,
    `dept_id` INT, 
    `hire_date` DATE, 
    `lastname_virtual` VARCHAR(50) GENERATED ALWAYS AS (`fullname` ->> '$.lastname'),
    PRIMARY KEY (`id`),
    INDEX `lastname_idx` ON `employees`(`lastname_virtual`)
);

Note that when applying indexes on generated column, the values are materialized and stored in the index. Due to this fact the table size is not increasing and we can take advantage of MySQL indexing.

Have questions? Contact us