How to Index JSON Fields in MySQL with Generated and Functional Indexes

Background

MySQL added native JSON support in version 5.7.8, allowing flexible storage of documents such as audit logs or user‑defined data. However, MySQL does not provide a direct way to create an index on a JSON column. Other databases typically use a Generalized Inverted Index (GIN), which MySQL lacks.

MySQL offers two indirect methods to index specific parts of a JSON document:

If you are on MySQL 5.7, create a generated column that extracts the desired value.

From MySQL 8.0.13 onward, you can create a functional index directly on the JSON expression.

Example Table

CREATE TABLE `activity_log` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `properties` json NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
);

Insert a sample JSON document into the properties column:

{
  "uuid": "e7af5df8-f477-4b9b-b074-ad72fe17f502",
  "request": {
    "email": "[email protected]",
    "firstName": "Little",
    "formType": "vehicle-inquiry",
    "lastName": "Bobby",
    "message": "Hello, can you tell me what the specs are for this vehicle?",
    "postcode": "75016",
    "townCity": "Dallas"
  }
}

The goal is to index the request.email key so that queries can quickly locate forms submitted by a specific email address.

Method 1 – Index via Generated Column

A generated column stores the result of an expression. It must be deterministic and return a scalar value.

First verify the extraction expression:

SELECT properties->>"$.request.email" FROM activity_log;

Result shows the email value, confirming the expression works.

Create a generated column named email that extracts the email address:

ALTER TABLE activity_log ADD COLUMN email VARCHAR(255) 
  GENERATED ALWAYS AS (properties->>"$.request.email");

MySQL automatically maintains this column; updating the JSON updates the generated value.

Add a regular B‑tree index on the generated column:

ALTER TABLE activity_log ADD INDEX email (email) USING BTREE;

Verify the index is used:

EXPLAIN SELECT * FROM activity_log WHERE email = '[email protected]';

The optimizer rewrites the query to use the email index, even when the original query references the JSON extraction operator.

Method 2 – Functional Index (MySQL 8.0.13+)

Starting with MySQL 8.0.13 you can create an index directly on an expression, skipping the generated column step:

ALTER TABLE activity_log ADD INDEX email ((properties->>"$.request.email")) USING BTREE;

This fails with:

ERROR: Cannot create a functional index on an expression that returns a BLOB or TEXT. Please consider using CAST.

MySQL infers the JSON extraction returns LONGTEXT, which cannot be indexed directly. Cast the result to a suitable type and set a binary collation:

ALTER TABLE activity_log ADD INDEX email ((
  CAST(properties->>"$.request.email" AS CHAR(255))
  COLLATE utf8mb4_bin
)) USING BTREE;

Run EXPLAIN again to confirm the functional index is employed.

Summary

Although MySQL does not support direct indexing of JSON columns, both generated columns (MySQL 5.7) and functional indexes (MySQL 8.0.13+) provide effective ways to index specific JSON fields, enabling fast look‑ups for complex data structures.