Multi-valued indexes on a JSON array, and the CAST

Filtering on a value inside a JSON array was a full scan until 8.0.17 added multi-valued indexes, and the syntax is unforgiving.

ALTER TABLE products ADD INDEX idx_tags (
  (CAST(attributes->'$.tags' AS CHAR(32) ARRAY))
);

-- and the ONLY functions that use it
SELECT * FROM products
WHERE JSON_CONTAINS(attributes->'$.tags', '"waterproof"');

SELECT * FROM products
WHERE 'waterproof' MEMBER OF (attributes->'$.tags');

-- this does NOT use it:
WHERE JSON_SEARCH(attributes, 'one', 'waterproof') IS NOT NULL;

The ARRAY keyword in the cast is what makes it multi-valued and omitting it produces a functional index that silently does not apply. Only three predicates can use it — MEMBER OF, JSON_CONTAINS and JSON_OVERLAPS — so the query has to be written to match the index rather than the other way around, and EXPLAIN is the only way to confirm.