[MDEV-40822] Study MySQL's JSON ARRAY indexing - Jira

XML

Word

Printable

Details

Type: Task
Status: Closed (View Workflow)
Priority: Major
Resolution: Done
Fix Version/s: N/A
Component/s: JSON
Labels:
- json
- json_indexing

Sprint:
Q3/2026 Server Development

Description

How ARRAY indexes work in MySQL. The goal is aiding the design of MDEV-40168 (actually MDEV-40777) in terms of compatibility and performance of the MariaDB MVI-on-fulltext.

1. Index definition

2. Supported datatypes

2.1 Inserting non-conforming JSON is not allowed

3. Unique indexes

4. Sargable predicates

4.1 JSON_OVERLAPS

4.2 JSON_CONTAINS

5. Producing query plans from the WHERE

5.1 Ranges are not joined together

5.2 Type-erasure: false positives and false negatives

5.2.1 Case 1

5.2.2 Case 2

5.2.3 Case 3 - MEMBER OF and temporal cast type

6. Multi-part keys

1. Index definition

Index is defined over CAST expr AS datatype ARRAY like so:

create index idx1 on  t1 ((cast(a->'$.arr' as unsigned array)));

create index idx1 on  t1 ((cast (json_extract(a, '$.arr') as unsigned array)));

create index idx1 on  t1 ((CAST(JSON_MERGE_PRESERVE(j->'$.tags', j->'$.attr') AS CHAR(6) ARRAY)));

2. Supported datatypes

See the function validate_cast_type_and_extract_length. Here is the list of all supported datatypes

SIGNED [INT]
UNSIGNED [INT]
DATE
TIME / TIME(p)
DATETIME / DATETIME(p)
DECIMAL / DECIMAL(m[,d])
CHAR(n) - length mandatory, n <= 512; charset must be unspecified (my_charset_bin)
BINARY(n) - length mandatory, n <= 512

If the extracted value is of string datatype, it will have binary collation for BINARY(n) and my_charset_utf8mb4_0900_bin ("utf8mb4_0900_bin") for CHAR(n) and will be compared with memcpy

*NOTE*: But datatypes are treated in an odd way, see Type-erasure: false positives and false negatives section below.
One place where the specified datatype is used is UNIQUE key enforcement: (link)

2.1 Inserting non-conforming JSON is not allowed

Attempt to insert a row where an array element evaluates to a JSON null or JSON array or JSON object results in an error.

Attempt to insert a row where an array element can't be parsed to a value of the target datatype results in a query error.

The same happens for INSERT IGNORE.

For example:

mysql> create table t1 (c int, j json, key idx ((CAST(j->'$.tags' AS UNSIGNED ARRAY))))engine=innodb;

Query OK, 0 rows affected (0.855 sec)

mysql> insert into t1 values (1, '{"tags": ["1", "34567"]}');

Query OK, 1 row affected (0.026 sec)

mysql> select cast(json_extract('[1, 42, "3"]', '$[2]') as unsigned);

+--------------------------------------------------------+

| cast(json_extract('[1, 42, "3"]', '$[2]') as unsigned) |

+--------------------------------------------------------+

|                                                      3 |

+--------------------------------------------------------+

1 row in set (0.001 sec)

mysql> insert into t1 values (1, '{"tags": ["1", "abcde", "34567"]}');

ERROR 3903 (22018): Invalid JSON value for CAST for functional index 'idx'.

mysql> select cast(json_extract('["1", "abcde", "34567"]', '$[1]') as unsigned);

+-------------------------------------------------------------------+

| cast(json_extract('["1", "abcde", "34567"]', '$[1]') as unsigned) |

+-------------------------------------------------------------------+

|                                                                 0 |

+-------------------------------------------------------------------+

1 row in set, 1 warning (0.001 sec)

mysql> show warnings;

+---------+------+--------------------------------------------------------------------------+

| Level   | Code | Message                                                                  |

+---------+------+--------------------------------------------------------------------------+

| Warning | 3156 | Invalid JSON value for CAST to INTEGER from column json_extract at row 1 |

+---------+------+--------------------------------------------------------------------------+

1 row in set (0.001 sec)

3. Unique indexes

Indexes using ARRAY may be UNIQUE. Attempt to insert a row where one (or more) of array elements overlap already existing row will fail.
Array elements in the same row may have duplicates.

CREATE TABLE mvi_uniq (

  id INT AUTO_INCREMENT PRIMARY KEY,

  attributes JSON,

  attr1 VARCHAR(32),

  UNIQUE INDEX idx_attributes ((CAST(attributes->'$.tags' AS BINARY(32) ARRAY))),

  INDEX idx_attr1 (attr1)

);

mysql> insert into mvi_uniq (attributes, attr1) values ('{ "tags": ["val1-10", "val1-11"] }', 'val1-11');

Query OK, 1 row affected (0.036 sec)

mysql> insert into mvi_uniq (attributes, attr1) values ('{ "tags": ["val1-99999949", "val1-10"] }', 'val1-10');

ERROR 1062 (23000): Duplicate entry '["base64:type15:dmFsMS0xMA==", "base64:type15:dmFsMS05OTk5OTk0OQ' for key 'mvi_uniq.idx_attributes'

4. Sargable predicates

These predicates are sargable:

JSON_OVERLAPS(json_doc1, json_doc2)
JSON_CONTAINS(json_doc, val[, path])
value MEMBER OF (json_array)

4.1 JSON_OVERLAPS

One of the arguments must point to the array being indexed. Say we have key idx ((CAST(attributes->'$.tags[]' AS CHAR(6) ARRAY))), this is sargable:

WHERE JSON_OVERLAPS(attributes->'$.tags', '["val1-0"]')

while this is not:

WHERE JSON_OVERLAPS(attributes, '{"tags":["val1-0"]}') ;

The path must point to the array:

where JSON_OVERLAPS('["val1-0", null]', attributes->'$.tags'); -- COND1

referring to "all array elements"

where JSON_OVERLAPS('["val1-0", null]', attributes->'$.tags[*]');  -- COND2

is equivalent for tags that are arrays, but is not handled by the optimizer. However, if the key is key idx ((CAST(attributes->'$.tags[*]' AS CHAR(6) ARRAY))), then the situation is flipped - COND1 results in full table scan but COND2 is sargable.

4.2 JSON_CONTAINS

Examples of sargable conditions

explain select * from mvi1 where json_contains(attributes->'$.tags', '"val1-0");

explain select * from mvi1 where json_contains(attributes->'$.tags', '["val1-1","val1-2"]');

Note that

where json_contains(attributes, '"val1-0"', '$.tags');

is not sargable even if it is identical. It appears that mysql only checks the first two arguments here and ignore the third, for example JSON_CONTAINS(attributes->'$.tags', '"val1-0"', '$.what[].ever') is sargable!

5. Producing query plans from the WHERE

Access path construction is integrated into the range optimizer. For query like

explain

SELECT id FROM mvi_benchmark WHERE

  'val1-10' MEMBER OF(attributes->'$.tags')

or

  'val1-12' MEMBER OF(attributes->'$.tags');

special kinds of ranges will be produced

  "range_scan_alternatives": [

      "index": "idx_attributes",

      "ranges": [

        "0x070076616c312d3132000...00 MEMBER OF (json_extract(attributes,_utf8mb4'$.tags'))",

        "0x070076616c312d3130000...00 MEMBER OF (json_extract(attributes,_utf8mb4'$.tags'))"

],

Range scans on these ranges can participate in index_merge.

explain

SELECT id FROM mvi_benchmark WHERE

  'val1-10' MEMBER OF(attributes->'$.tags')

or

  attr1='val1-11';

5.1 Ranges are not joined together

ORed conditions are not joined together:

explain

select * from mvi1

where

  'val1' MEMBER OF(attributes->'$.tags')  or

  'val1' MEMBER OF(attributes->'$.tags') ;

gives

  "ranges": [

    "'val1' MEMBER OF (json_extract(attributes,_utf8mb4'$.tags'))",

    "'val1' MEMBER OF (json_extract(attributes,_utf8mb4'$.tags'))"

],

for AND-ed conditions

where

  'val1' MEMBER OF(attributes->'$.tags')  AND

  'val2' MEMBER OF(attributes->'$.tags') ;

it just picks the first one

"ranges": [

  "'val2' MEMBER OF (json_extract(attributes,_utf8mb4'$.tags'))"

],

5.2 Type-erasure: false positives and false negatives

It appears that MySQL casts every array element to the declared type, indexes that, and the optimizer casts the query constant the same way.

This causes the following bugs:

5.2.1 Case 1

mysql> create table t1 (c int, j json, key idx ((CAST(j->'$.tags' AS CHAR(6) ARRAY))))engine=innodb;

Query OK, 0 rows affected (0.232 sec)

mysql> insert into t1 values (1, '{"tags": [1, "abcde", "34567", "", 34567]}');

Query OK, 1 row affected (0.046 sec)

mysql> select * from t1 where json_contains(j->'$.tags', '1.0');

Empty set (0.012 sec)

mysql> alter table t1 drop index idx;

Query OK, 0 rows affected (0.155 sec)

Records: 0  Duplicates: 0  Warnings: 0

mysql> select * from t1 where json_contains(j->'$.tags', '1.0');

+------+--------------------------------------------+

| c    | j                                          |

+------+--------------------------------------------+

|    1 | {"tags": [1, "abcde", "34567", "", 34567]} |

+------+--------------------------------------------+

1 row in set (0.011 sec)

JSON_CONTAINS compares JSON values according to their JSON type (so, JSON number 1 in the array and the lookup value JSON number 1.0 are equal).
But the index compares them as CHAR(6) so "1.0" is not equal to "1", and we get a false negative.

5.2.2 Case 2

mysql> alter table t1 add index idx ((CAST(j->'$.tags' AS CHAR(6) ARRAY)));

Query OK, 0 rows affected (0.162 sec)

Records: 0  Duplicates: 0  Warnings: 0

mysql> select * from t1 where json_contains(j->'$.tags', '"1"');

+------+--------------------------------------------+

| c    | j                                          |

+------+--------------------------------------------+

|    1 | {"tags": [1, "abcde", "34567", "", 34567]} |

+------+--------------------------------------------+

1 row in set (0.003 sec)

mysql> alter table t1 drop index idx;

Query OK, 0 rows affected (0.793 sec)

Records: 0  Duplicates: 0  Warnings: 0

mysql> select * from t1 where json_contains(j->'$.tags', '"1"');

Empty set (0.019 sec)

Here, JSON_CONTAINS() compares JSON string "1" and JSON number 1 and considers them unequal.
However, in the index they are both VARCHARs and are considered equal.

So, we get a non-matching record. Normally, the optimizer re-evaluates conditions used for range access, one can expect that JSON_CONTAINS() would be re-evaluated and filter the condition away. Apparently that didn't happen.

The EXPLAIN shows the JSON_CONTAINS() predicate is present:

    "condition": "json_contains(cast(json_extract(j,_utf8mb4'$.tags') as char(6) array),json'[\"1\"]')",

but unlike in the original WHERE clause, it uses CAST ... AS ARRAY and json'...' constructs which are not accepted by the parser. Apparently this form evaluated to TRUE.
In the case without index, the EXPLAIN shows this (which evaluates to FALSE):

    "condition": "json_contains(json_extract(t1.j,'$.tags'),<cache>('\"1\"'))",

5.2.3 Case 3 - MEMBER OF and temporal cast type

MEMBER OF predicate is also affected

create table t4 (c int, j json, key idx ((CAST(j->'$.tags' AS DATE ARRAY))))engine=innodb;

insert into t4

select a, concat('{"tags": ["',cast(date_add('2026-01-01', interval a day ) as date)  ,'"]}')

from one_k;

mysql> select * from t4 where '2026-1-1' member of (j->'$.tags');

+------+--------------------------+

| c    | j                        |

+------+--------------------------+

|    0 | {"tags": ["2026-01-01"]} |

+------+--------------------------+

1 row in set (0.002 sec)

mysql> select * from t4 use index() where '2026-1-1' member of (j->'$.tags');

Empty set (0.039 sec)

mysql> explain select * from t4  where '2026-1-1' member of (j->'$.tags')\G

*************************** 1. row ***************************

EXPLAIN: -> Filter: json'"2026-01-01"' member of (cast(json_extract(j,_utf8mb4'$.tags') as date array))  (cost=0.35 rows=1)

    -> Index lookup on t4 using idx (cast(json_extract(j,_utf8mb4'$.tags') as date array) = json'"2026-01-01"')  (cost=0.35 rows=1)

1 row in set (0.002 sec)

Observe that the condition in the query

  '2026-1-1' member of ...

became

  json'"2026-01-01"' member of ...

That is, the lookup value was converted into DATE, and then into a JSON string literal...

Note that the query plan doesn't have to use the ARRAY index.
It is sufficient that the index exists.

Add another index and get a query that uses it:

alter table t4 add index(c);

mysql> explain select * from t4  where '2026-1-1' member of (j->'$.tags') and c=0\G

*************************** 1. row ***************************

EXPLAIN: -> Filter: json'"2026-01-01"' member of (cast(json_extract(j,_utf8mb4'$.tags') as date array))  (cost=0.255 rows=0.05)

    -> Index lookup on t4 using c (c = 0)  (cost=0.255 rows=1)

1 row in set (0.002 sec)

mysql> select * from t4  where '2026-1-1' member of (j->'$.tags') and c=0;

+------+--------------------------+

| c    | j                        |

+------+--------------------------+

|    0 | {"tags": ["2026-01-01"]} |

+------+--------------------------+

1 row in set (0.002 sec)

The result is as if comparison used dates.

mysql> select * from t4 ignore index(idx)  where '2026-1-1' member of (j->'$.tags') and c=0;

Empty set (0.002 sec)

Does the ARRAY index serve as a kind of "schema description for fields in JSON document" ? If yes, why does it honor the optimizer hints to ignore it?

However, "ARRAY index as schema of data in JSON fields" is not consistently followed.
Not sure if this is actually necessary, but I had this INSERT done:

mysql> insert into t4 values (5000, '{"tags": ["2027-1-1"]}');

Query OK, 1 row affected (0.017 sec)

Then, lets copy the JSON_CONTAINS from the WHERE to SELECT list:

select

  c, j,

  json_contains(j->'$.tags', '"2027-01-01"')  as where_cond

from t4

where

   json_contains(j->'$.tags', '"2027-01-01"')   -- same as above.

   and c=5000;

+------+------------------------+------------+

| c    | j                      | where_cond |

+------+------------------------+------------+

| 5000 | {"tags": ["2027-1-1"]} |          0 |

+------+------------------------+------------+

1 row in set (0.003 sec)

So, where_cond evaluates to FALSE for the returned row

Different result of matching JSON_CONTAINS() predicate will also occur inside the WHERE clause if the JSON_CONTAINS is not in the top-level AND/OR:

select

  c, j,

  json_contains(j->'$.tags', '"2027-01-01"') as where_cond

from t4

where

  json_contains(j->'$.tags', '"2027-01-01"') and c=5000 and

  IF(json_contains(j->'$.tags', '"2027-01-01"'), 1, 0);

Empty set (0.002 sec)

6. Multi-part keys

Since processing is integrated into the range optimizer, it is possible to support multi-part keys and ranges, where one key part is an ARRAY.

MySQL requires that in the index, only one keypart uses ARRAY indexing.

TODO: How important this is?
TODO: Can we skip supporting this in the first milestone?

Attachments

Issue Links

is part of

MDEV-40168 Indexes over JSON data based on Fulltext index

In Progress

Activity

People

Assignee:: Yuchen Pei

Reporter:: Sergei Petrunia

Votes:: 0 Vote for this issue

Watchers:: 5 Start watching this issue

Dates

Created:: 2026-08-12 14:43

Updated:: 2026-09-25 09:42

Resolved:: 2026-09-25 09:42

Time Tracking

Estimated:

Remaining:

Logged:

Not Specified

Git Integration

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.