Indexing JSON column in MySQL 8

Question

So I'm experimenting with json column. Mysql 8.0.17 supposed to work with multi value JSON indexes, like this:

CREATE INDEX data__nbr_idx ON a1( (CAST(data->'$.nbr' AS UNSIGNED ARRAY)) )

I have column categories with JSON like this ["books", "clothes"]. I need to get all products from "books" category. I can use "json_contains" or new "member of".

SELECT * FROM products WHERE JSON_CONTAINS(categories, '\"books\"')
SELECT * FROM products WHERE "books" MEMBER OF(categories)

And it works. The problem is that of course EXPLAIN will reveal that there queries are making full table scan, and because of that it is slow.

So I need some index.

I changed index example by replacing "unsigned" type with "char(32) since my categories are strings and not numbers. I cannot find any example for this in google so I assumed that char() will be fine, but not.

This is my index query:

CREATE INDEX categories_index ON products((CAST(categories AS CHAR(32) ARRAY)))

also tried

CREATE INDEX categories_index ON products((CAST(categories->'$' AS CHAR(32) ARRAY)))

but selects are still making full table scan. What I'm doing wrong? How to correctly index json column without using virtual columns?

user_6248528 · Answer

For a multi-valued json index, the json paths have to match, so with an index

CREATE INDEX categories_index 
ON products((CAST(categories->'$' AS CHAR(32) ARRAY)))

your query has to also use the path ->'$' (or the equivalent json_extract(...,'$'))

SELECT * FROM products WHERE "books" MEMBER OF(categories->'$')

Make it consistent and it should work.

It seems though that an index without an explicit path does not work as expected, so you have to specify ->'$' if you want to use the whole document. This is probably a bug, but could also be an intended behaviour of casting or autocasting to an array. If you specify the path you'll be on the safe side.

Indexing JSON column in MySQL 8

1 Answers