JSON Comparator: does MariaDB need one?

MySQL has a "JSON comparator" [1]. This MDEV is about studying whether MariaDB needs one.

• Does it allow to query JSON documents better ? (Not looking at how indexing is arranged)
• Would it be useful with current vcol-based indexes?
• Would it help with "fulltext-like" indexes (like in MDEV-25848) ?

Task Take-Aways:

MySQL's JSON Comparator allows to compare JSON values, however it is NOT used for any index access. So, its utility is very limited at the moment. It seems to be not worth implementing.

MySQL's Multi-Valued Indexes do NOT use JSON Comparator. A Multi-Valued Index is defined over an array of values of a specific type, for example:

INDEX(CAST(js_column->"$.array_attribute" AS UNSIGNED ARRAY))

and then one can do containment searches using the specified datatype.

JSON Comparator would be useful if we implemented something like MonoDB's Wildcard Indexes. Wildcard Index indexes both attribute name and value. This allows to use the index for ad-hoc queries for arbitrary attributes:

  db.orders_table.find({ "priority": "urgent" }) // priority=urgent

  db.orders_table.find({ "size": { $gt: 80 } })  // size > 80

This is a good use case for document databases. Getting this in MariaDB would require a lot of work on the storage engine side, though.

See also comment linked below for a review of various mongodb index types with an emphasis on comparator use and a comparison between mysql multi-valued indexes and mongodb multi-key indexes.

https://jira.mariadb.org/browse/MDEV-37288?focusedCommentId=310846&page=com.atlassian.jira.plugin.system.issuetabpanels%3Acomment-tabpanel#comment-310846

Further details

Using JSON Comparator

One can compare JSON values with literals:

create table t100 (a json);

insert into t100 values ('10'), ('9'),('"foo"'),('"bar"');

mysql> select a, a > 10 from t100;

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

| a     | a > 10 |

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

| 10    |      0 |

| 9     |      0 |

| "foo" |      1 |

| "bar" |      1 |

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

4 rows in set (0.00 sec)

and that produces a more meaningful result.

Without JSON Comparator, one has to use CAST(JSON_VALUE(...) AS type) to get the desired comparison.

JSON Comparator and indexing: Nope

One might think they could JSON Comparator for B-Tree indexing, but that is explicitly disallowed:

mysql> alter table t200 add index((js->"col1"));

ERROR 3753 (42000): Cannot create a functional index on a function that returns a JSON or GEOMETRY value.

This looks odd: why would one support a comparison operation (e.g. select * from tbl where json_col=1) that can't be handled efficiently?

Relationship with Multi-Valued Indexes (none?)

MySQL's Multi-Valued Indexes ([3]) allow one record to produce multiple index entries.
Syntactically, it looks like a BTREE index on CAST( json_expr AS type ARRAY):

create table t100 (a json);

insert into t100 values ('{ "arr":[1,2,4,5]}');

insert into t100 values ('{ "arr":[]}');

-- insert into t100 values ('{}');  -- crashes MySQL.

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

The optimizer may use Multi-Valued index with when searches are performed with these functions:

• MEMBER OF()
• JSON_CONTAINS()
• JSON_OVERLAPS()

mysql> explain select * from t101 where 4 member of (a->'$.arr');

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

| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref   | rows | filtered | Extra       |

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

|  1 | SIMPLE      | t101  | NULL       | ref  | idx1          | idx1 | 9       | const |    1 |   100.00 | Using where |

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

Note that CAST(... AS datatype ARRAY) specifies the datatype.
So, JSON comparator doesn't seem necessary here.

(also note: these indexes seem similar to MongoDB's Multikey indexes).

Also note that Multi-Valued Index has limitations on the number of keys one row can have [3] says:

The maximum number of values per record for a multi-valued index is determined by the amount of data than can be stored on a single undo log page, which is 65221 bytes...
Tests have shown a multi-valued index to permit as many as 1604 integer keys per record, for example.

Note that this doesn't apply to having the same key in multiple records. That doesn't seem to be limited.

What would JSON Comparator be useful for

Small usecase: If we had indexes like MySQL's multi-valued indexes but supported arrays that contained values of different types.

Hypothetical primary use-case: Wildcard-like indexes

MongoDB calls those Wildcard indexes.

Consider a table storing JSON documents like:

{

  "name" : "Widget",

  "color":  "red",

  "size": 60,

  "price": 10

}

where we don't have a list of all possible attributes (and that's why we store JSON).

Suppose indexing would put these values in a B-Tree-like index:

"name": "Widget", rowid1

"color": "red", rowid2 

"color": "blue", rowid3

"color": "green", rowid4

"price": 5, rowid8

"price": 10, rowid2

Items are first ordered by their name, then by value, using the JSON comparator.
Then, one could make ad-hoc queries like "Give me all rows with price <= 10", or "Give me all rows with color = green".
The use of comparator here is essential as "price" may be integer, double, or even string.

Links

[1] - JSON Comparator WL: https://dev.mysql.com/worklog/task/?id=8249#:~:text=Comparing%20JSON%20values%20with%20other,%3E%20and%20operators.
[2] - https://dev.mysql.com/doc/refman/9.4/en/create-table-secondary-indexes.html#json-column-indirect-index
[3] - https://dev.mysql.com/doc/refman/9.4/en/create-index.html#create-index-multi-valued
[4] - https://www.mongodb.com/docs/manual/core/indexes/index-types/index-wildcard/create-wildcard-index-single-field/