MySQL How to Index a JSON array?

Question

I have a JSON field with one-dimensional array. In fact, in this field I have a list of some IDs, like this:

[347470, 162063, 17315, 346852, 174776, 295865, 7833, 136813]

In my queries I refer this field like this:

... AND JSON_CONTAINS(`users_actions`, 174776)=0 

My question is: should I create an index for this field, and if so - which exactly index should I use?

Answer 1

If you are running a very recent version of MySQL (8.0.17 or higher), you can use a Multi-valued index, which was designed exactly for that purpose:

A multi-valued index is a secondary index defined on a column that stores an array of values.

[...]

Multi-valued indexes are intended for indexing JSON arrays.

[...]

The optimizer uses a multi-valued index to fetch records when the following functions are specified in a WHERE clause: MEMBER OF(), JSON_CONTAINS(), JSON_OVERLAPS().

Assuming that your json array is stored in column myjs of table mytable, you can create the index like so:

CREATE INDEX myidx 
ON mytable ( (CAST(myjs AS UNSIGNED ARRAY)) );