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?

Do you mean [like this](https://mysqlserverteam.com/indexing-json-documents-via-virtual-columns/)? — tadman, Aug 19 '20 at 00:10
It's worth noting that Postgres does a fantastic job of this on `ARRAY,`, `HSTORE` and `JSON` columns. MySQL not so much. — tadman, Aug 19 '20 at 00:10
I don't think there's a way to index this in MySQL. You would be better off normalizing the schema. — Barmar, Aug 19 '20 at 00:19
@tadman As far as I understand, stored generated columns are good if I'd have a multi-dimensional array in my JSON field — Red October, Aug 19 '20 at 00:20
@tadman: agree with Barmar, that technique works for JSON objects, not for arrays. — GMB, Aug 19 '20 at 00:28
IMO, anytime you reference a JSON column in a WHERE clause, it's a red flag. You should normalize the data. — Bill Karwin, Aug 19 '20 at 00:30

score 15 · Accepted Answer · answered Aug 19 '20 at 00:23

15

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)) );

answered Aug 19 '20 at 00:23

GMB

216,147
25
84
135

How do we index an array which contains objects ? – Sudhir N Jun 26 '23 at 09:05
@SudhirN: this type of index supports scalar values only, hence so far we [can't do that in MySQL](https://dbfiddle.uk/6a-V4ZOj): *Cannot store an array or an object in a scalar key part of the index* – GMB Jun 26 '23 at 13:46
so we cant have an index if its an array of objects – Sudhir N Jun 26 '23 at 15:18
@SudhirN: no - as you can see in the fiddle that I linked in my comment. – GMB Jun 26 '23 at 15:40
I see... is that your tool, dbfiddle ? nice would love to see it evolve – Sudhir N Jun 26 '23 at 16:46

MySQL How to Index a JSON array?

1 Answers1