Indexing JSON

Indexing JSON

Indexing JSON with Generated and Multi-Valued Indexes

A JSON column cannot be indexed directly. For a scalar path, index a generated column or a functional index (Generated Columns). For an array, MySQL 8.0.17 524 added the multi-valued index, CAST(expr AS type ARRAY), with one entry per element, serving MEMBER OF(), JSON_CONTAINS() and JSON_OVERLAPS(). Before it existed, the MEMBER OF query below was a table scan.

A multi-valued index on tags and a virtual column on pagesSQL

ALTER TABLE products
  ADD INDEX idx_tags ((CAST(attributes->'$.tags' AS CHAR(20) ARRAY))),
  ADD COLUMN pages SMALLINT UNSIGNED AS (attributes->>'$.pages') VIRTUAL,
  ADD INDEX idx_pages (pages);
EXPLAIN FORMAT=JSON INTO @m
  SELECT sku FROM products WHERE 'php' MEMBER OF (attributes->'$.tags');
EXPLAIN FORMAT=JSON INTO @o
  SELECT sku FROM products WHERE JSON_OVERLAPS(attributes->'$.tags', '["sql", "linux"]');
EXPLAIN FORMAT=JSON INTO @p SELECT sku FROM products WHERE attributes->>'$.pages' > 400;
SELECT JSON_EXTRACT(@m, '$**.index_access_type', '$**.index_name') AS member_of,
       JSON_EXTRACT(@o, '$**.index_access_type', '$**.index_name') AS overlaps,
       JSON_EXTRACT(@p, '$**.index_access_type', '$**.index_name') AS pages_expr\G

Output

*************************** 1. row ***************************
 member_of: ["index_lookup", "idx_tags"]
  overlaps: ["index_range_scan", "idx_tags"]
pages_expr: ["index_range_scan", "idx_pages"]

The last query never named pages, but its expression matched the column's definition, so the optimizer substituted idx_pages. A multi-valued index allows one array key part, cannot be primary or covering, and rejects a JSON null element (error 3903). JSON comparison is case-sensitive, so 'PHP' MEMBER OF (...) matched 0 rows against 2 for 'php'.

MENU