How to create an index on a JSON array using MySQL 8

Question

I have a JSON field in mysql 8 that looks like this:

[{"key": "apples", "string_values": ["red"]}, {"key": "oranges", "string_values": ["orange"]}]

Is there a way to create an index on string values? I was hoping I could do something like this:

ALTER TABLE mytable ADD INDEX myindex( 
    (CAST(metadata->'$[*].string_values' AS UNSIGNED ARRAY)) 
);

However that returns the following error: Cannot store an array or an object in a scalar key part of the index

Fiddle for example: https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=ae36a5169094ae67a290febf09f49f0c

EDIT:

I'm starting to think that the only way I can do this is with a generated column:

The following select will hit the index now.

ALTER TABLE mytable ADD string_values JSON AS (JSON_EXTRACT(metadata, '$[*].string_values[0]'));

ALTER TABLE mytable ADD INDEX idx_string_values( 
    (CAST(string_values->'$' AS CHAR(100) ARRAY))
);

SELECT * FROM mytable WHERE 'red' MEMBER OF(string_values->'$');

@Akina the table value I included above has an extra trailing curly brace. The dbfiddle you are running isn't showing the error. If you remove the trailing brance you see the same error. Updated question and fiddle: dbfiddle.uk/… — Jesse
– Jesse, Commented Mar 12, 2021 at 13:04
I'm not 100% sure if it will work in your case, but in my tests the index is used even when the json field is queried directly. — txigreman
– txigreman, Commented Oct 27, 2021 at 12:55
In other words: you need the virtual column to create the index, but you don't need to use that column for the index to be hitted. — txigreman
– txigreman, Commented Oct 27, 2021 at 13:05

Akina · Accepted Answer · 2021-03-12 14:49:30Z

There are 2 separate problems.

First is solvable. The next fiddle describes it:

CREATE TABLE mytable (metadata JSON);
INSERT INTO mytable VALUES ('[{"key": "apples", "string_values": ["red"]}, {"key": "oranges", "string_values": ["orange"]}]');

-- you try to index arrays
SELECT metadata->'$[*].string_values' FROM mytable;
-- but you need in scalar values
SELECT metadata->'$[*].string_values[0]' FROM mytable;

| metadata->'$[*].string_values' |
| :----------------------------- |
| [["red"], ["orange"]]          |

| metadata->'$[*].string_values[0]' |
| :-------------------------------- |
| ["red", "orange"]                 |

db<>fiddle here

Second problem is unsolvable.

Look carefully to the statement which you use when you try to create index:

ALTER TABLE mytable ADD INDEX myindex((CAST(metadata->'$[*].string_values' AS UNSIGNED ARRAY)));

The values in the array ["red", "orange"] are NOT numeric.

jurez · Accepted Answer · 2021-03-12 04:30:37Z

Before answering I should make a short disclaimer. Storing JSON data in SQL database actually goes against the fundamental SQL design philosophy. SQL, as its name suggests, is for structured, i.e. strongly-typed data. If you pass your data to a SQL database as JSON, you are essentially discarding its structure and treating it like a string.

Therefore, in such a situation I would first consider the following two options:

(A) Can I treat this data as structured? In this case, I would create a table where columns correspond to JSON fields, then create an index like for any other column.

(B) Is my data fundamentally unstructured? In such case, I would try to model it as key-value pairs in SQL, or use a no-SQL database such as CouchDB or Elasticsearch.

With key-value-based approach, you can separate the property in question into a separate table (for array that would be key/index/value) which you can then use for indexing. This of course adds some overhead which, depending on circumstances, might or might not be acceptable in your case.

However, there are still many legitimate reasons why something might not be classfied either as A or B. If you have already considered and tried both approaches and you can clearly justify why your case is exceptional:

(C) Read the MySQL documentation about indexing generated columns

Even with this, you will probably still need to remodel your JSON data, such as convert an array into individual entries (id/index/value).

Collectives™ on Stack Overflow

How to create an index on a JSON array using MySQL 8

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related