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

3. Generating fulltext tokens

4. Sanitizing table writes

5. Hooking to the optimizer

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;

			}

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"'

5. Hooking to the optimizer

TODO: do we actually want to convert every array element to the cast datatype and add it to the fulltext index? The current wip patch does so, as does mysql, which causes bugs (see Section "5.2 Type-erasure: false positives and false negatives" of MDEV-40822)

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

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?

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