CODEWITHSUNDEEP
mysqlmysql-8.0
Jesse
Jesse

Reputation: 8393

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

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

Upvotes: 2

Views: 3522

Answers (2)

Akina
Akina

Reputation: 42642

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.

Upvotes: 3

jurez
jurez

Reputation: 4667

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

Upvotes: 0

Related Questions