JSON indexing: ARRAY index over fulltext

This is about implementing Goal #1 from MDEV-40168, assuming we're doing everything from scratch.

See TODO-6222 for description of what MySQL supports.
We want to support something similar.

Contents

1. Syntax support

2. Hooking to fulltext index

2.1. Alternative design to avoid materialising tokens (in a virtual column)

3. Generating fulltext tokens

4. Sanitizing table writes

5. Hooking to the optimizer

Option 0: inject MATCH items to top-level WHERE

Option 1: integrate fully with the range optimizer.

Option 2: Special kind of quick select

Option 3: Do something else

6. Query execution

7. Record estimates

8. Prior art

9. Implementation steps

1. Syntax support

Support syntax like

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

2. Hooking to fulltext index

This should create a user-invisible virtual column and a fulltext index over it (with a custom fulltext parser). Note this is like special case of functional index MDEV-35853. "like" because of the fulltext and encoding (see below), and "special" because only (CAST ... AS <DATATYPE> ARRAY) is supported.

It is advantageous for the virtual column data to be in a binary form than JSON array, with proper encoding e.g. hex(pad(sort_string(val))):

1. Value is already the correct format (e.g. 1 vs "1") with CAST
2. So are numerical types (e.g. 1 vs 1.0 vs 1e0)
3. Metacharacters are "escaped" by hexxing
4. ft_min_word_len is satisfied with padding

It may need to satisfy the following condition so likely not BLOB/VARBINARY, but something like latin1_bin TEXT/VARCHAR (TODO: check):

  bool is_good_for_ft() const

  {

    // Binary and UCS2/UTF16/UTF32 are not supported

    return m_charset != &my_charset_bin && m_charset->mbminlen == 1;

  }

Further, the virtual column needs to be stored to have innodb fulltext index support 1907 ER_UNSUPPORTED_ACTION_ON_GENERATED_COLUMN:

/* in create_table_info_t::gcols_in_fulltext_or_spatial: */

			/* We do not support special (Fulltext or

			Spatial) index on virtual columns */

			if (!key->key_part[j].field->stored_in_db()) {

				my_error(ER_UNSUPPORTED_ACTION_ON_GENERATED_COLUMN, MYF(0));

				return true;

			}

See comment below https://jira.mariadb.org/browse/MDEV-40777?focusedCommentId=338993&page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#comment-338993 for Claude's explanation why so.

Upon further prompting, Claude claims that allowing vcol fts to innodb is not too big a project and sizes it about the same as 1TA/1TB below. Let's call this approach 1VV. See comment below https://jira.mariadb.org/browse/MDEV-40777?focusedCommentId=339343&page=com.atlassian.jira.plugin.system.issuetabpanels%3Acomment-tabpanel#comment-339343

2.1. Alternative design to avoid materialising tokens (in a virtual column)

The general idea is that an internal ft parser reuses encode_mvi_key and emit keys directly into the ft index (let's call this Approach 1T and the original 1V. T for tokenizer and V for virtual column). See comment blow for a summary written by Claude. Either use a dummy virtual column to store the index metadata (Variant A), or use "WITH PARSER mvi" to have the fulltext index directly on the column, though the latter requires a new frm field to hold the metadata (Variant B) - see the same comment. Adding a stored virtual column also causes adding mvi to an existing table to require COPY - see the same comment https://jira.mariadb.org/browse/MDEV-40777?focusedCommentId=338994&page=com.atlassian.jira.plugin.system.issuetabpanels%3Acomment-tabpanel#comment-338994

However, note both variants would cause loss of expressiveness: the first argument of the index is restricted from arbitrary expressions to <column>-><path>.

3. Generating fulltext tokens

For ARRAY indexes, the fulltext parser is basic: a table row

{ "foo": [ "aaa", "bbb", "cccccc" ] }

should produce a sequence of tokens representing all the elements in the array of interest:

aaa

bbb

cccccc

As previously discussed, it is likely better if we emit the elements in binary form than text form.

Note that here we depart from ft_json design (POC-COMMIT-1 in MDEV-40168 description) where each token has "<path>=<value>" format, but rather simply "<value>" (modulo encoding).

The encoding should be done at the server layer (due to the type ignorance of fulltext parser etc.), and likely we could simply use the default internal fulltext parser.

In sum, create a new Item_func_mvi_encoding (say sql function named MVI_ENCODE), and desugar

ALTER TABLE t1 ADD INDEX idx ((CAST(a->'$.arr' AS <DATATYPE> ARRAY));

into something like

ALTER TABLE t1

  ADD COLUMN MV_DB_1 TEXT CHARACTER SET latin1 COLLATE latin1_bin

    AS (MVI_ENCODE(a->'$.arr', <DATATYPE>)) PERSISTENT,

  ADD FULLTEXT INDEX idx(MV_DB_1);

Note that unlike mysql, there's no direct casting from JSON scalar to target temporal types i.e. DATE, DATETIME, and TIME:

select cast(json_extract('["2026-08-08", "abcde", "34567"]', '$[0]') as date);

cast(json_extract('["2026-08-08", "abcde", "34567"]', '$[0]') as date)

NULL

Warnings:

Warning	1292	Incorrect datetime value: '"2026-08-08"'

select cast(json_extract('["2026-08-08 12:34:56", "abcde", "34567"]', '$[0]') as datetime);

cast(json_extract('["2026-08-08 12:34:56", "abcde", "34567"]', '$[0]') as datetime)

NULL

Warnings:

Warning	1292	Incorrect datetime value: '"2026-08-08 12:34:56"'

select cast(json_extract('["12:34:56", "abcde", "34567"]', '$[0]') as time);

cast(json_extract('["12:34:56", "abcde", "34567"]', '$[0]') as time)

NULL

Warnings:

Warning	1292	Incorrect time value: '"12:34:56"'

4. Sanitizing table writes

TODO-6222 section "Supported datatypes" describes how MySQL refuses to insert rows into table that cause errors when generating values for the ARRAY index.

How do we do the same?

This can be done while doing the encoding, i.e. in Item_func_mvi_encode::val_str().

On a related note, this looks like a bug (opened MDEV-40931):

select cast('[1, 42, "3"]'->'$[2]' as int);

cast('[1, 42, "3"]'->'$[2]' as int)

0

Warnings:

Warning	1292	Truncated incorrect INTEGER value: '"3"'

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

cast('[1, 42, "3"]'->'$[2]' as unsigned)

0

Warnings:

Warning	1292	Truncated incorrect INTEGER value: '"3"'

We could reject incompatible data, or we could accept them but do not encode them (current patch approach). Either way a recheck is necessary to root out false positives (see below).

There are some traps with datatypes, manifested in mysql as false negative and false positive type bugs, see Section "5.2 Type-erasure: false positives and false negatives" of MDEV-40822.

To address the false negatives ((cast ... as char(N) array), number 1 in array and number 1.0 in query), do not use the index if the cast type is incompatible with the constant type. We could (and probably should) emit "Cannot use key for lookup" warnings (like MDEV-32203 did it for regular conditions that the optimizer finds non-sargable because of datatype/collation mismatches).

To address the false positives, the results returned by the index will need to be recheked. This is true regardless of whether invalid rows are rejected or not: even if they are rejected, we still need to contend with "2020-01-01" vs "2020-1-1" for DATE cast type. It is a question what is to be done when/if we will implement skipping of JSON_CONTAINS check

5. Hooking to the optimizer

TODO: should we piggy-pack on the range optimizer like MySQL does? Options:

Option 0: inject MATCH items to top-level WHERE

E.g.

# transform

WHERE JSON_CONTAINS(j->".arr", '["abc", "def"]')

# to

WHERE JSON_CONTAINS(j->".arr", '["abc", "def"]') AND MATCH(DB_MVI_1) AGAINST('+616263 +646566' IN BOOLEAN MODE)

This leaves the optimizer to choose the fulltext index.

bb-main-mdev-40168 7cfaf2b83eeb47e1eff2032bb255a428a6399919 implements an initial version of this. It iterates over top-level WHERE conjunctions and add a MATCH for each item.

With some extension, it is possible to also combine two JSON_CONTAINS of the same index, i.e. e.g.

# transform

where json_contains(j->'$.tags','"abcde"') and json_contains(j->'$.tags','"34567"')

# to

where json_contains(j->'$.tags','"abcde"') and json_contains(j->'$.tags','"34567"') and match(...) against('+6162636465 +3334353637' in boolean mode)

and even disjunctions e.g.

# transform

where json_contains(j->'$.tags','"abcde"') or json_contains(j->'$.tags','"34567"')

# to

where (json_contains(j->'$.tags','"abcde"') or json_contains(j->'$.tags','"34567"')) and match(...) against('6162636465 3334353637' in boolean mode)

Option 1: integrate fully with the range optimizer.

This will give similar expressive power and behavior to MySQL.

Implement get_mm_leaf, that is, produce an elementary SEL_ARG object from a sargable predicate.

Extend SEL_ARG objects to also represent "Array-type intervals". Then let the range optimizer process these together with other kinds of interval.

Minus: MySQL provides little to combine ranges (see TODO-6222) :

• OR-ing two ARRAY-type intervals doesn't eliminate the overlap.
• AND-ing "r1 AND r2" just discards the r2.

Pluses:

• capability to produce index_merge plans ( json_expr OR key_expr)
• capability to handle multi-part indexes. (but we don't do it in MySQL)

What cost and #rows to assume? Start with some guesstimates, then see "Estimates" section below.

Option 2: Special kind of quick select

Do our own: walk the WHERE's AND/OR tree, find the eligible predicates, create potential access method(s).
Then, still create a QUICK_SELECT_I object. Note that fulltext scans currently use FT_SELECT objects which inherit from QUICK_RANGE_SELECT (should that actually be QUICK_SELECT_I?)

What cost and #rows to assume? Start with some guesstimates, then see "Estimates" section below.

Option 3: Code something other than quick select

TODO: why do this, what advantages?

6. Query execution

Based on the query plan, construct a fulltext query and use the fulltext engine to read rows.

7. Estimation

It seems, innodb does provide some estimates about word frequencies:

SET GLOBAL innodb_ft_aux_table = 'db/tbl';   -- global, needs SUPER

SELECT word, SUM(doc_count) AS docs

FROM (SELECT DISTINCT word, first_doc_id, doc_count

      FROM information_schema.INNODB_FT_INDEX_TABLE

      WHERE word IN ('foo','bar')) t

GROUP BY word;

Can these be used to provide #rows estimates?
EDIT The above reflects the internal fulltext index organization. Under the hood, full text index is 6 partitions (by the first character in 'word') of this:

CREATE TABLE $FTS_PREFIX_INDEX_[1-6](

    word          VARCHAR(FTS_MAX_WORD_LEN),

    first_doc_id  INT NOT NULL,

    last_doc_id   UNSIGNED NOT NULL,

    doc_count     UNSIGNED INT NOT NULL,

    ilist         VARBINARY NOT NULL,

    UNIQUE CLUSTERED INDEX ON (word, first_doc_id))

Conceptually, it should be possible to make a records_in_range() call over this table?
See this commit with proof-of-concept records_in_range()-like call:

commit c43d9b0989c430d5c6496da8e29e6b6bcf800ecb (origin/bb-main-mdev-40168-estimates, bb-main-mdev-40168-estimates)

Author: Sergei Petrunia <sergey@mariadb.com>

Date:   Wed Sep 2 22:03:14 2026 +0300

    MDEV-40168: JSON-over-fulltext: add estimates.

    Add records_in_range-like estimates for fulltext index

8. Prior art

MDEV-371 creates an invisible virtual column internally and a B-tree index on it. It lists 65 caused issues. Might be worth checking for pitfalls of this task.

9. Implementation steps

Step 1
Make the parser accept the CREATE TABLE/CREATE INDEX syntax like shown in section 1. Since the implementation is not present, the CREATE TABLE/INDEX should just fail
with some error that's not "parse error".

Step 2
For CREATE TABLE/INDEX statements creating ARRAY index (further called "create-array-index statement"), create a stored virtual column that will be indexed.

Step 3
Now that we have a virtual column, create a fulltext index with custom ft parser.
The parser doesn't matter now, can use Serg's example or a dummy ft parser.
At this point, the above CREATE TABLE query is accepted and succeeds.
INSERTs may fail.

Step 4
Pin down the data encoding/fulltext token format and generate tokens.

Step 5
Basic index-based querying, Option 0.
Support only `JSON_CONTAINS()` function, only when it has two arguments.
That is, we only handle the case where the whole WHERE clause is just one JSON_CONTAINS predicate. This is enough to run testing.

Step 6
Basic index-based querying, Option 2. Same as Step 5 but go with Option 2. Including costing - hook with cost estimation code / come up with some cost formula.

Step 7+

• Add support for JSON_OVERLAPS
• Fix SHOW CREATE TABLE and INFORMATION_SCHEMA.STATISTICS (see https://github.com/MariaDB/server/pull/5620#issuecomment-5534079267 and https://github.com/MariaDB/server/pull/5620#discussion_r3902547465)
• Support MEMBER OF operator (MDEV-38591, currently In-review)
• support cast to non-string/int types
• remove vcol and do computation directly to index (Approach 1T, see Section 2.1 above)
• test combinations - same results using or not using mvi
• multi part keys
• UPDATE/DELETE support https://jira.mariadb.org/browse/MDEV-40777?focusedCommentId=338827&page=com.atlassian.jira.plugin.system.issuetabpanels%3Acomment-tabpanel#comment-338827
• ALTER TABLE drops MVI (psergei TODO) https://jira.mariadb.org/browse/MDEV-40777?focusedCommentId=338837&page=com.atlassian.jira.plugin.system.issuetabpanels%3Acomment-tabpanel#comment-338837
• int array cannot be created https://jira.mariadb.org/browse/MDEV-40777?focusedCommentId=338846&page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#comment-338846
• Move the elaborate commit comments to sql/opt_multi_valued_index.cc etc.
• More efficient encoding, e.g. using base32 instead of hex
• + all the TODOs in the commits