Як індексувати поля JSON у MySQL, зокрема масиви?

Колонку JSON у MySQL неможливо проіндексувати напряму. Індексують значення, витягнуті з документа.

1. Скалярне поле - функціональний індекс чи згенерована колонка:

-- функціональний індекс (MySQL 8.0.13+)
CREATE INDEX products_brand_idx ON products ((CAST(attributes ->> '$.brand' AS CHAR(100)) COLLATE utf8mb4_bin));

-- або віртуальна колонка з індексом - простіше в запитах
ALTER TABLE products
    ADD COLUMN brand VARCHAR(100) AS (attributes ->> '$.brand') VIRTUAL,
    ADD INDEX (brand);

->> повертає текст з типом LONGTEXT, тож у функціональному індексі потрібне явне CAST до типу з обмеженою довжиною. А вираз у запиті має дослівно збігатися з виразом індексу - тому віртуальна колонка надійніша: у запиті пишуть просто WHERE brand = ?.

2. Масив - багатозначний індекс (multi-valued index, MySQL 8.0.17+). Один рядок дає кілька записів в індексі - по одному на кожен елемент масиву:

-- products.attributes = {"tags": ["laravel", "php", "api"]}
CREATE INDEX products_tags_idx ON products ((CAST(attributes -> '$.tags' AS CHAR(50) ARRAY)));

SELECT * FROM products WHERE 'php' MEMBER OF (attributes -> '$.tags');
SELECT * FROM products WHERE JSON_CONTAINS(attributes -> '$.tags', '["php", "api"]');
SELECT * FROM products WHERE JSON_OVERLAPS(attributes -> '$.tags', '["vue", "react"]');

Індекс використовується лише цими трьома конструкціями: MEMBER OF, JSON_CONTAINS, JSON_OVERLAPS.

Обмеження багатозначних індексів:

• один багатозначний компонент на індекс;
• лише для простих масивів скалярних значень;
• не підтримуються ORDER BY за індексом і деякі типи;
• оновлення масиву оновлює всі його записи в індексі.

Порівняння з PostgreSQL: там GIN-індекс на весь jsonb прискорює пошук за будь-якими ключами без попереднього вибору полів. У MySQL треба заздалегідь знати, за якими полями шукатимуть.

Сигнал до зміни схеми: якщо за полем JSON постійно фільтрують, сортують і з'єднують, - йому краще бути звичайною колонкою (а масиву - таблицею зв'язку).

Докладніше в документації: CREATE INDEX: багатозначні індекси