Support creation of Full-Text Index of a Sub-Document

Full-Text indexes allow searching the entire document (or a sub-document) for any text value contained.

Full-Text search of JSON documents may require a custom fulltext parser and/or additional querying extensions to be fully useful (e.g. json_textcontains() from Oracle).

External Requirements

A xprotocol client

X1

MUST be able to create a FULLTEXT index on document of a collection

X2

MUST be able to create a FULLTEXT index on a sub-document of a collection

X3

MUST be able to optionally specify the name of a fulltext parser for a FULLTEXT index

X4

MUST be able to specify the name of the index

X5

MUST be able to specify if the (sub-)document has to exist

X6

MUST be able to drop the index by name via the drop_index admin command.

X7

MUST receive an Error message if the create_fulltext_index operation fails.

Internal Requirements

The create_fulltext_index operation

F1

MUST fail if generated ALTER TABLE statement fails like

• wrong fulltext parser
• names too long
• index with the same name exists
• generated column with the same name exists

F2

MUST fail if a 'unique' FULLTEXT is requested

Background

In the xplugin the create_collection_index StmtExecute command of the mysqlx namespace is used to create indexes for collections.

See: admin_cmd_handler.cc

It takes the following arguments:

• schema: string
• collection: string
• name: string
• unique: bool
• type: string [optional, default='INDEX', allowed=INDEX|SPATIAL]
• constraint: object or list-of-objects
  • member: docpath
  • required: bool
  • type: string

Adding FULLTEXT support

new type: FULLTEXT

A new value for .type is to be supported in the index field specification:

FULLTEXT

new, optional .with_parser

To specify the fulltext parser plugin a FULLTEXT index may specify:

with_parser: string

default constraint.type

If no constraint.type is given, it defaults to:

TEXT(64)

parameter schema

• schema: string
• collection: string
• name: string
• unique: bool
• type: string [optional, default='INDEX', allowed=INDEX|SPATIAL|FULLTEXT]
• with_parser: string [optional, default=empty]
• constraint: object or list-of-objects
  • member: docpath
  • required: bool
  • type: string [optional]

SQL generation

ALTER TABLE, ADD COLUMN part:

ADD COLUMN <col> TEXT
  GENERATED ALWAYS AS (JSON_EXTRACT(doc, <member>)) STORED

Notes:

• FULLTEXT indexes can currently only be created on STORED generated columns, not VIRTUAL
• FULLTEXT indexes cannot be created on a JSON column
• FULLTEXT indexes allow NULLable columns

ALTER TABLE, ADD INDEX part:

ADD FULLTEXT INDEX <name> (<col>) WITH PARSER <with_parser>

Notes:

• the content of with_parser expects an identifier. Use identifier quoting if special characters are used.

Error and Warnings

Example

• schema: foo
• collection: col1
• name: ftidx
• unique: FALSE
• type: FULLTEXT
• with_parser: ngram
• constraint:
  • member: '$.subdoc'
  • required: FALSE

results in:

ALTER TABLE col1
  ADD COLUMN ft TEXT
    GENERATED ALWAYS AS (doc->>'$.subdoc')) STORED,
  ADD FULLTEXT INDEX ftidx (ft) WITH PARSER ngram;

If with_parser is not specified:

ALTER TABLE col1
  ADD COLUMN ft TEXT
    GENERATED ALWAYS AS (doc->>'$.subdoc')) STORED,
  ADD FULLTEXT INDEX ftidx (ft);