How to create index on json column in MySQL?

For indexing values stored in JSON, use a stored generated column.

In example, for indexing title and category of

{"title": "Some Title", "category": "Some Category", "url": "...", ...}

use something like:

CREATE TABLE listings (  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,  title VARCHAR(255) AS    (data->>'$.title') STORED,  category VARCHAR(255) AS    (data->>'$.category') STORED,  data JSON NOT NULL,  KEY (title),           -- index title  KEY (category),        -- index category  KEY (title, category)  -- composite index of title & category);

Read more about MySQL as smart JSON storage :-)

JSON columns, like columns of other binary types, are not indexed directly; instead, you can create an index on a generated column that extracts a scalar value from the JSON column. See Section “Secondary Indexes and Generated Virtual Columns”, for a detailed example.

With MySQL 8.0.21 release it is possible to use this syntax:

CREATE TABLE inventory(items JSON,INDEX i1 ( (JSON_VALUE(items, '$.name' RETURNING CHAR(50))) ),INDEX i2 ( (JSON_VALUE(items, '$.price' RETURNING DECIMAL(5,2))) ),INDEX i3 ( (JSON_VALUE(items, '$.quantity' RETURNING UNSIGNED)) ));

and query using:

SELECT items->"$.price" FROM inventoryWHERE JSON_VALUE(items, '$.name' RETURNING VARCHAR(50)) = "hat";SELECT * FROM inventoryWHERE JSON_VALUE(items, '$.price' RETURNING DECIMAL(5,2)) <= 100.01;SELECT items->"$.name" AS item, items->"$.price" AS amountFROM inventoryWHERE JSON_VALUE(items, '$.quantity' RETURNING UNSIGNED) > 500;

source: https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-21.html