X DevAPI User Guide
To make large collections of documents more efficient to navigate you can create an index based on one or more fields found in the documents in the collection. This section describes how to index a collection.
Collection indexes are ordinary MySQL indexes on virtual
columns that extract data from the documents in the
collection. Currently MySQL cannot index JSON values directly,
therefore to enable indexing of a collection you provide a
JSON document which specifies the document's fields used by
the index. You pass the JSON document defining the index as
the IndexDefinition parameter to the
Collection.createIndex(
method.
This example shows how to create a mandatory integer type
index based on the field name
,
IndexDefinition
)count
:
myCollection.createIndex("count", {fields:[{"field": "$.count", "type":"INT", required:true}]});
This example shows how to create an index based on a text field, in this case, a zip code. For a text field, you must specify a prefix length for the index, as required by MySQL Server:
myCollection.createIndex("zip", {fields: [{field: "$.zip", type: "TEXT(10)"}]})
See Defining an Index for information about the format of the JSON document used to define fields as MySQL types, and the currently supported MySQL types.
The Collection.createIndex()
method fails
with an error if an index with the same name already exists or
if the index definition is not correctly formed. The name
parameter is required and must be a valid index name as
accepted by the SQL statement CREATE
INDEX
.
To remove an existing index use the
collection.dropIndex(string name)
method.
This would delete the index with the name passed in, and the
operation silently succeeds if the named index does not exist.
As the indexes of a collection are stored as virtual columns,
to verify a created index use the SHOW
INDEX
statement. For example to use this SQL from
MySQL Shell:
session.runSql('SHOW INDEX FROM mySchema.myCollection');
To create an index based on the documents in a collection you
need to create an IndexDefinition
JSON
document. This section explains the valid fields you can use
in such a JSON document to define an index.
To define a document field to index a collection on, the type
of that field must be uniform across the whole collection. In
other words the type must be consistent. The JSON document
used for defining an index, such as {fields: [{field:
'$.username', type: 'TEXT'}]}
, can contain the
following:
fields
: an array of at least one
IndexField
object, each of which
describes a JSON document field to be included in the
index.
A single IndexField
description
consists of the following fields:
field
: a string with the full
document path to the document member or field to be
indexed
type
: a string with one of the
supported column types to map the field to (see
Field Data Types
). For numeric types, the optional
UNSIGNED
keyword can follow. For
the TEXT
type you must define the
length to consider for indexing (the prefix length).
required
: an optional boolean, set
to true
if the field is required to
exist in the document. Defaults to
false
for all types except
GEOJSON
, which defaults to
true
.
options
: an optional integer, used
as special option flags to use when decoding
GEOJSON
data.
srid
: an optional integer, srid
value to use when decoding GEOJSON
data.
array
: (for MySQL 8.0.17 and later)
an optional boolean, set to true
if
the field contains arrays. The default value is
false
. See
Indexing Array Fields for
details.
For MySQL 8.0.16 and earlier, fields that are JSON arrays are not supported in the index; specifying a field that contains array data does not generate an error from the server, but the index does not function correctly.
type
: an optional string which defines
the type of index. One of INDEX
or
SPATIAL
. The default is
INDEX
and can be omitted.
For example, to create an index based on multiple fields, issue:
myCollection.createIndex('myIndex', {fields: [{field: '$.myField', type: 'TEXT'}, // {field: '$.myField2', type: 'TEXT(10)'}, {field: '$.myField3', type: 'INT'}]})
Including any other fields in an
IndexDefinition
or
IndexField
JSON document which is not
described here causes
collection.createIndex()
to fail with an
error.
If index type is not specified, or is set to
INDEX
then the resulting index is created
in the same way as would be created after issuing
CREATE INDEX
. If index type is
set to SPATIAL
then the created index is
the same as would be created after issuing
CREATE INDEX
with the
SPATIAL
keyword, see
SPATIAL Index Optimization and
Creating Spatial Indexes. For example
myCollection.createIndex('myIndex', // {fields: [{field: '$.myGeoJsonField', type: 'GEOJSON', required: true}], type:'SPATIAL'})
When using the SPATIAL
type of index the
required
field cannot be set to
false
in IndexField
entries.
The values of indexed fields are converted from JSON to the
type specified in the IndexField
description using standard MySQL type conversions (see
Type Conversion in Expression Evaluation), except for the
GEOJSON
type which uses the
ST_GeomFromGeoJSON()
function
for conversion. This means that when using a numeric type in
an IndexField
description and the actual
field value is non-numeric, it is converted to 0.
The options
and srid
fields in IndexField
can only be present if
type
is set to GEOJSON
.
If present, they are used as parameters for
ST_GeomFromGeoJSON()
when
converting GEOJSON
data into MySQL native
GEOMETRY
values.
The following data types are supported for document fields. Type descriptions are not case-sensitive.
For MySQL 8.0.17 and later, X DevAPI supports creating
indexes based on array fields by setting the Boolean
array
field in the
IndexField
description to
true
. For example, to create an index on
the emails
array field:
collection.createIndex("emails_idx", // {fields: [{"field": "$.emails", "type":"CHAR(128)", "array": true}]});
The following restrictions apply to creating indexes based on arrays:
For each index, only one indexed field can be an
array
Data types for which index on arrays can be created: