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?

It's worth noting that Postgres does a fantastic job of this on ARRAY,, HSTORE and JSON columns. MySQL not so much. — tadman
– tadman, Commented Aug 19, 2020 at 0:10
I don't think there's a way to index this in MySQL. You would be better off normalizing the schema. — Barmar
– Barmar, Commented Aug 19, 2020 at 0:19
@tadman: agree with Barmar, that technique works for JSON objects, not for arrays. — GMB
– GMB, Commented Aug 19, 2020 at 0:28

GMB · Accepted Answer · 2020-08-19 00:23:58Z

18

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, 2020 at 0:23

GMB

224k25 gold badges103 silver badges151 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Sudhir N Over a year ago

How do we index an array which contains objects ?

GMB Over a year ago

@SudhirN: this type of index supports scalar values only, hence so far we can't do that in MySQL: Cannot store an array or an object in a scalar key part of the index

Sudhir N Over a year ago

so we cant have an index if its an array of objects

GMB Over a year ago

@SudhirN: no - as you can see in the fiddle that I linked in my comment.

Sudhir N Over a year ago

I see... is that your tool, dbfiddle ? nice would love to see it evolve

Collectives™ on Stack Overflow

MySQL How to Index a JSON array?

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related