How to create index on json column in MySQL?

Question

How to create index on sub-documents in Json data type in MySQL server?

I know we have to create a generated column from the base table and then need to index that column Virtually or stored.

But I want syntax for creating a generated column for sub-document.

Answer 1

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 inventory
WHERE JSON_VALUE(items, '$.name' RETURNING VARCHAR(50)) = "hat";

SELECT * FROM inventory
WHERE JSON_VALUE(items, '$.price' RETURNING DECIMAL(5,2)) <= 100.01;

SELECT items->"$.name" AS item, items->"$.price" AS amount
FROM inventory
WHERE JSON_VALUE(items, '$.quantity' RETURNING UNSIGNED) > 500;

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