Can MySQL dynamicly create indexes for JSON column?

Question

Given you have a JSON column called slug structured like this:

{
    "en_US": "slug",
    "nl_NL": "nl/different-slug"
}

This could be indexed by adding generated columns to the table that point to the values of en_US and nl_NL. This works fine but adding a third locale would require a table schema update.

Would it be possible to let MySQL automagicly index all the key value pairs in the JSON without explicitly defining them in the schema?

How would you use those indices?

Paul Spiegel
– Paul Spiegel

2018-08-12 16:46:02 +00:00
Commented Aug 12, 2018 at 16:46 — Paul Spiegel
– Paul Spiegel, Commented Aug 12, 2018 at 16:46
To speed up querying against a slug

Boyd
– Boyd

2018-08-12 17:31:39 +00:00
Commented Aug 12, 2018 at 17:31 — Boyd
– Boyd, Commented Aug 12, 2018 at 17:31

Shadow · Accepted Answer · 2018-08-12 20:32:54Z

1

As mysql manual on json data type says:

JSON columns, like columns of other binary types, are not indexed directly; instead, you can create an index on a generated column that extracts a scalar value from the JSON column. See Indexing a Generated Column to Provide a JSON Column Index, for a detailed example.

So, the answer is no, mysql cannot index the contents of a json column automatically. You need to define and index generated columns.

answered Aug 12, 2018 at 20:32

Shadow

34.5k10 gold badges67 silver badges76 bronze badges

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

Collectives™ on Stack Overflow

Can MySQL dynamicly create indexes for JSON column?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related