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.
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 (see JSON Notes section)
CAST( JSON_UNQUOTE( JSON_EXTRACT(json_doc, path) ) AS type) but I don't think I can do a INDEX i1 ( CAST( JSON_UNQUOTE( JSON_EXTRACT(json_doc, path) ) AS type) ). Idk. All these questions because I am too lazy to spin up a test environment.JSON_VALUE does not accept VARCHAR should be changed to CHAR. For differences between the two: dev.mysql.com/doc/refman/8.0/en/char.html
CREATE INDEX says Functional indexes are implemented as hidden virtual generated columns so I first thought this answer was not performant, but it actually is performant (see this). Turned out just a documentation bug.For indexing values stored in JSON, use a stored generated column.
For example, to index 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 it in my article 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.
