Navigation
This is an upcoming (i.e. in progress) version of the manual.

createIndexes

Definition

createIndexes

Builds one or more indexes on a collection.

The createIndexes command takes the following form:

db.runCommand(
  {
    createIndexes: <collection>,
    indexes: [
        {
            key: {
                <key-value_pair>,
                <key-value_pair>,
                ...
            },
            name: <index_name>,
            <option1>,
            <option2>,
            ...
        },
        { ... },
        { ... }
    ],
    writeConcern: { <write concern> }
  }
)

The createIndexes command takes the following fields:

Field Type Description
createIndexes string The collection for which to create indexes.
indexes array Specifies the indexes to create. Each document in the array specifies a separate index.
writeConcern document

Optional. A document expressing the write concern. Omit to use the default write concern.

New in version 3.4.

Each document in the indexes array can take the following fields:

Changed in version 3.0: The dropDups option is no longer available.

Field Type Description
key document

Specifies the index’s fields. For each field, specify a key-value pair in which the key is the name of the field to index and the value is either the index direction or index type. If specifying direction, specify 1 for ascending or -1 for descending.

MongoDB supports several different index types including text, geospatial, and hashed indexes. See index types for more information.

Changed in version 4.2: MongoDB 4.2 wildcard indexes support workloads where users query against custom fields or a large variety of fields in a collection:

  • To create a wildcard index on all fields and subfields in a document, specify { "$**" : 1 } as the index key. You cannot specify a descending index key when creating a wildcard index.

    You can also either include or exclude specific fields and their subfields from the index using the optional wildcardProjection parameter.

    Wildcard indexes omit the _id field by default. To include the _id field in the wildcard index, you must explicitly include it in the wildcardProjection document (i.e. { "_id" : 1 }).

  • You can create a wildcard index on a specific field and its subpaths by specifying the full path to that field as the index key and append "$**" to the path:

    { "path.to.field.$**" : 1 }

    You cannot specify a descending index key when creating a wildcard index.

    The path-specific wildcard index syntax is incompatible with the wildcardProjection option. You cannot specify additional inclusions or exclusions on the specified path.

The wildcard index key must use one of the syntaxes listed above. For example, you cannot specify a compound index key. For more complete documentation on wildcard indexes, including restrictions on their creation, see Wildcard Index Restrictions.

The mongod featureCompatibilityVersion must be 4.2 to create wildcard indexes. For instructions on setting the fCV, see Set Feature Compatibility Version on MongoDB 4.2 Deployments.

For examples of wildcard index creation, see Create a Wildcard Index.

name string A name that uniquely identifies the index.
background boolean

Optional. Deprecated in MongoDB 4.2.

  • For feature compatibility version (fcv) "4.0", specifying background: true directs MongoDB to build the index in the background. Background builds do not block operations on the collection. The default value is false.

  • Changed in version 4.2.

    For feature compatibility version (fcv) "4.2", all index builds use an optimized build process that holds the exclusive lock only at the beginning and end of the build process. The rest of the build process yields to interleaving read and write operations. MongoDB ignores the background option if specified.

unique boolean

Optional. Creates a unique index so that the collection will not accept insertion or update of documents where the index key value matches an existing value in the index.

Specify true to create a unique index. The default value is false.

The option is unavailable for hashed indexes.

partialFilterExpression document

Optional. If specified, the index only references documents that match the filter expression. See Partial Indexes for more information.

A filter expression can include:

You can specify a partialFilterExpression option for all MongoDB index types.

New in version 3.2.

sparse boolean

Optional. If true, the index only references documents with the specified field. These indexes use less space but behave differently in some situations (particularly sorts). The default value is false. See Sparse Indexes for more information.

Changed in version 3.2: Starting in MongoDB 3.2, MongoDB provides the option to create partial indexes. Partial indexes offer a superset of the functionality of sparse indexes. If you are using MongoDB 3.2 or later, partial indexes should be preferred over sparse indexes.

Changed in version 2.6: 2dsphere indexes are sparse by default and ignore this option. For a compound index that includes 2dsphere index key(s) along with keys of other types, only the 2dsphere index fields determine whether the index references a document.

2d, geoHaystack, and text indexes behave similarly to the 2dsphere indexes.

expireAfterSeconds integer Optional. Specifies a value, in seconds, as a TTL to control how long MongoDB retains documents in this collection. See Expire Data from Collections by Setting TTL for more information on this functionality. This applies only to TTL indexes.
storageEngine document

Optional. Allows users to configure the storage engine on a per-index basis when creating an index.

The storageEngine option should take the following form:

storageEngine: { <storage-engine-name>: <options> }

Storage engine configuration options specified when creating indexes are validated and logged to the oplog during replication to support replica sets with members that use different storage engines.

New in version 3.0.

weights document Optional. For text indexes, a document that contains field and weight pairs. The weight is an integer ranging from 1 to 99,999 and denotes the significance of the field relative to the other indexed fields in terms of the score. You can specify weights for some or all the indexed fields. See Control Search Results with Weights to adjust the scores. The default value is 1.
default_language string Optional. For text indexes, the language that determines the list of stop words and the rules for the stemmer and tokenizer. See Text Search Languages for the available languages and Specify a Language for Text Index for more information and examples. The default value is english.
language_override string Optional. For text indexes, the name of the field, in the collection’s documents, that contains the override language for the document. The default value is language. See Use any Field to Specify the Language for a Document for an example.
textIndexVersion integer

Optional. The text index version number. Users can use this option to override the default version number.

For available versions, see Versions.

New in version 2.6.

2dsphereIndexVersion integer

Optional. The 2dsphere index version number. Users can use this option to override the default version number.

For the available versions, see Versions.

New in version 2.6.

bits integer

Optional. For 2d indexes, the number of precision of the stored geohash value of the location data.

The bits value ranges from 1 to 32 inclusive. The default value is 26.

min number Optional. For 2d indexes, the lower inclusive boundary for the longitude and latitude values. The default value is -180.0.
max number Optional. For 2d indexes, the upper inclusive boundary for the longitude and latitude values. The default value is 180.0.
bucketSize number

For geoHaystack indexes, specify the number of units within which to group the location values; i.e. group in the same bucket those location values that are within the specified number of units to each other.

The value must be greater than 0.

collation document

Optional. Specifies the collation for the index.

Collation allows users to specify language-specific rules for string comparison, such as rules for lettercase and accent marks.

If you have specified a collation at the collection level, then:

  • If you do not specify a collation when creating the index, MongoDB creates the index with the collection’s default collation.
  • If you do specify a collation when creating the index, MongoDB creates the index with the specified collation.

The collation option has the following syntax:

collation: {
   locale: <string>,
   caseLevel: <boolean>,
   caseFirst: <string>,
   strength: <int>,
   numericOrdering: <boolean>,
   alternate: <string>,
   maxVariable: <string>,
   backwards: <boolean>
}

When specifying collation, the locale field is mandatory; all other collation fields are optional. For descriptions of the fields, see Collation Document.

New in version 3.4.

wildcardProjection document

Optional. Allows users to include or exclude specific field paths from a wildcard index using the { "$**" : 1} key pattern. This option is only valid if creating a wildcard index on all document fields. You cannot specify this option if creating a wildcard index on a specific field path and its subfields, e.g. { "path.to.field.$**" : 1 }

The wildcardProjection option takes the following form:

wildcardProjection: {
  "path.to.field.a" : <value>,
  "path.to.field.b" : <value>
}

The <value> can be either of the following:

  • 1 or true to include the field in the wildcard index.
  • 0 or false to exclude the field from the wildcard index.

A wildcardProjection cannot contain both include and exclude specifications, with the exception of including the _id field while excluding other fields.

Note

As MongoDB includes an index on _id by default, wildcard indexes omit the _id field. To include the _id field in the wildcard index, you must explicitly include it in the wildcardProjection document:

"wildcardProjection" : {
  "_id" : 1
}

The _id field is the only field you can specify along with field exclusions.

The mongo shell provides the methods db.collection.createIndex() and db.collection.createIndexes() as wrappers for the createIndexes command.

Considerations

Changed in version 3.2: MongoDB disallows the creation of version 0 indexes. To upgrade existing version 0 indexes, see Version 0 Indexes.

Index Names

Changed in MongoDB 4.2

Starting in version 4.2, for featureCompatibilityVersion set to "4.2" or greater, MongoDB removes the Index Name Length limit of 127 byte maximum. In previous versions or MongoDB versions with featureCompatibilityVersion (fCV) set to "4.0", index names must fall within the limit.

Starting in version 4.2, the createIndexes command and the mongo shell helpers db.collection.createIndex() and db.collection.createIndexes() report an error if you create an index with one name, and then try to create the same index again but with another name.

{
   "ok" : 0,
   "errmsg" : "Index with name: x_1 already exists with a different name",
   "code" : 85,
   "codeName" : "IndexOptionsConflict"
}

In previous versions, MongoDB did not create the index again, but would return a response object with ok value of 1 and a note that implied that the index was not recreated. For example:

{
   "numIndexesBefore" : 2,
   "numIndexesAfter" : 2,
   "note" : "all indexes already exist",
   "ok" : 1
}

Replica Sets and Sharded Clusters

To minimize the impact of building an index on replica sets and sharded clusters, use a rolling index build procedure as described on Build Indexes on Replica Sets.

Collation and Index Types

The following indexes only support simple binary comparison and do not support collation:

Tip

To create a text, a 2d, or a geoHaystack index on a collection that has a non-simple collation, you must explicitly specify {collation: {locale: "simple"} } when creating the index.

Behavior

Concurrency

MongoDB index builds against a populated collection require an exclusive read-write lock against the collection. Operations that require a read or write lock on the collection must wait until the mongod releases the lock.

  • For featureCompatibilityVersion "4.2". MongoDB uses an optimized build process that holds the exclusive lock only at the beginning and end of the build process. The rest of the build process yields to interleaving read and write operations.
  • For featureCompatibilityVersion "4.0", MongoDB by default holds the exclusive collection lock for the entire index build. background index builds do not take an exclusive collection lock.

For complete documentation on the index build process, see Index Builds on Populated Collections.

Memory Usage Limit

Changed in version 3.4: createIndexes supports building one or more indexes on a collection. createIndexes uses a combination of memory and temporary files on disk to complete index builds. The default limit on memory usage for createIndexes is 500 megabytes, shared between all indexes built using a single createIndexes command. Once the memory limit is reached, createIndexes uses temporary disk files in a subdirectory named _tmp within the --dbpath directory to complete the build.

You can override the memory limit by setting the maxIndexBuildMemoryUsageMegabytes server parameter. Setting a higher memory limit may result in faster completion of index builds larger than 500 megabytes. However, setting this limit too high relative to the unused RAM on your system can result in memory errors.

Index Options

With the exception of the collation option, if you create an index with one set of index options and then try to recreate the same index but with different index options, MongoDB will not change the options nor recreate the index.

To change these index options, drop the existing index with db.collection.dropIndex() before running createIndexes with the new options.

Collation Option

New in version 3.4.

Unlike other index options, you can create multiple indexes on the same key(s) with different collations. To create indexes with the same key pattern but different collations, you must supply unique index names.

If you have specified a collation at the collection level, then:

  • If you do not specify a collation when creating the index, MongoDB creates the index with the collection’s default collation.
  • If you do specify a collation when creating the index, MongoDB creates the index with the specified collation.

Tip

By specifying a collation strength of 1 or 2, you can create a case-insensitive index. Index with a collation strength of 1 is both diacritic- and case-insensitive.

To use an index for string comparisons, an operation must also specify the same collation. That is, an index with a collation cannot support an operation that performs string comparisons on the indexed fields if the operation specifies a different collation.

For example, the collection myColl has an index on a string field category with the collation locale "fr".

db.myColl.createIndex( { category: 1 }, { collation: { locale: "fr" } } )

The following query operation, which specifies the same collation as the index, can use the index:

db.myColl.find( { category: "cafe" } ).collation( { locale: "fr" } )

However, the following query operation, which by default uses the “simple” binary collator, cannot use the index:

db.myColl.find( { category: "cafe" } )

For a compound index where the index prefix keys are not strings, arrays, and embedded documents, an operation that specifies a different collation can still use the index to support comparisons on the index prefix keys.

For example, the collection myColl has a compound index on the numeric fields score and price and the string field category; the index is created with the collation locale "fr" for string comparisons:

db.myColl.createIndex(
   { score: 1, price: 1, category: 1 },
   { collation: { locale: "fr" } } )

The following operations, which use "simple" binary collation for string comparisons, can use the index:

db.myColl.find( { score: 5 } ).sort( { price: 1 } )
db.myColl.find( { score: 5, price: { $gt: NumberDecimal( "10" ) } } ).sort( { price: 1 } )

The following operation, which uses "simple" binary collation for string comparisons on the indexed category field, can use the index to fulfill only the score: 5 portion of the query:

db.myColl.find( { score: 5, category: "cafe" } )

Wildcard Indexes

New in version 4.2.

For examples of wildcard index creation, see Create a Wildcard Index. For complete documentation on Wildcard Indexes, see Wildcard Indexes.

Example

The following command builds two indexes on the inventory collection of the products database:

db.getSiblingDB("products").runCommand(
  {
    createIndexes: "inventory",
    indexes: [
        {
            key: {
                item: 1,
                manufacturer: 1,
                model: 1
            },
            name: "item_manufacturer_model",
            unique: true
        },
        {
            key: {
                item: 1,
                supplier: 1,
                model: 1
            },
            name: "item_supplier_model",
            unique: true
        }
    ],
    writeConcern: { w: "majority" }
  }
)

When the indexes successfully finish building, MongoDB returns a results document that includes a status of "ok" : 1.

Create a Wildcard Index

New in version 4.2: The mongod featureCompatibilityVersion must be 4.2 to create wildcard indexes. For instructions on setting the fCV, see Set Feature Compatibility Version on MongoDB 4.2 Deployments.

For complete documentation on Wildcard Indexes, see Wildcard Indexes.

The following lists examples of wildcard index creation:

Create a Wildcard Index on a Single Field Path

Consider a collection products_catalog where documents may contain a product_attributes field. The product_attributes field can contain arbitrary nested fields, including embedded documents and arrays:

{
  "_id" : ObjectId("5c1d358bf383fbee028aea0b"),
  "product_name" : "Blaster Gauntlet",
  "product_attributes" : {
     "price" : {
       "cost" : 299.99
       "currency" : USD
     }
     ...
  }
},
{
  "_id" : ObjectId("5c1d358bf383fbee028aea0c"),
  "product_name" : "Super Suit",
  "product_attributes" : {
     "superFlight" : true,
     "resistance" : [ "Bludgeoning", "Piercing", "Slashing" ]
     ...
  },
}

The following operation creates a wildcard index on the product_attributes field:

use inventory
db.runCommand(
  {
    createIndexes: "products_catalog",
    indexes: [
      {
        key: { "product_attributes.$**" : 1 },
        name: "wildcardIndex"
      }
    ]
  }
)

With this wildcard index, MongoDB indexes all scalar values of product_attributes. If the field is a nested document or array, the wildcard index recurses into the document/array and indexes all scalar fields in the document/array.

The wildcard index can support arbitrary single-field queries on product_attributes or one of its nested fields:

db.products_catalog.find( { "product_attributes.superFlight" : true } )
db.products_catalog.find( { "product_attributes.maxSpeed" : { $gt : 20 } } )
db.products_catalog.find( { "product_attributes.elements" : { $eq: "water" } } )

Create a Wildcard Index on All Field Paths

Consider a collection products_catalog where documents may contain a product_attributes field. The product_attributes field can contain arbitrary nested fields, including embedded documents and arrays:

{
  "_id" : ObjectId("5c1d358bf383fbee028aea0b"),
  "product_name" : "Blaster Gauntlet",
  "product_attributes" : {
     "price" : {
       "cost" : 299.99
       "currency" : USD
     }
     ...
  }
},
{
  "_id" : ObjectId("5c1d358bf383fbee028aea0c"),
  "product_name" : "Super Suit",
  "product_attributes" : {
     "superFlight" : true,
     "resistance" : [ "Bludgeoning", "Piercing", "Slashing" ]
     ...
  },
}

The following operation creates a wildcard index on all scalar fields (excluding the _id field):

use inventory
db.runCommand(
  {
    createIndexes: "products_catalog",
    indexes: [
      {
        key: { "$**" : 1 },
        name: "wildcardIndex"
      }
    ]
  }
)

With this wildcard index, MongoDB indexes all scalar fields for each document in the collection. If a given field is a nested document or array, the wildcard index recurses into the document/array and indexes all scalar fields in the document/array.

The created index can support queries on any arbitrary field within documents in the collection:

db.products_catalog.find( { "product_price" : { $lt : 25 } } )
db.products_catalog.find( { "product_attributes.elements" : { $eq: "water" } } )

Wildcard indexes omit the _id field by default. To include the _id field in the wildcard index, you must explicitly include it in the wildcardProjection document (i.e. { "_id" : 1 }).

Create a Wildcard Index on Multiple Specific Field Paths

Consider a collection products_catalog where documents may contain a product_attributes field. The product_attributes field can contain arbitrary nested fields, including embedded documents and arrays:

{
  "_id" : ObjectId("5c1d358bf383fbee028aea0b"),
  "product_name" : "Blaster Gauntlet",
  "product_attributes" : {
     "price" : {
       "cost" : 299.99
       "currency" : USD
     }
     ...
  }
},
{
  "_id" : ObjectId("5c1d358bf383fbee028aea0c"),
  "product_name" : "Super Suit",
  "product_attributes" : {
     "superFlight" : true,
     "resistance" : [ "Bludgeoning", "Piercing", "Slashing" ]
     ...
  },
}

The following operation creates a wildcard index and uses the wildcardProjection option to include only scalar values of the product_attributes.elements and product_attributes.resistance fields in the index.

use inventory
db.runCommand(
  {
    createIndexes: "products_catalog",
    indexes: [
      {
        key: { "$**" : 1 },
        "wildcardProjection" : {
          "product_attributes.elements" : 1,
          "product_attributes.resistance" : 1
        },
        name: "wildcardIndex"
      }
    ]
  }
)

While the key pattern "$**" covers all fields in the document, the wildcardProjection field limits the index to only the included fields and their nested fields.

If a field is a nested document or array, the wildcard index recurses into the document/array and indexes all scalar fields in the document/array.

The created index can support queries on any scalar field included in the wildcardProjection:

db.products_catalog.find( { "product_attributes.elements" : { $eq: "Water" } } )
db.products_catalog.find( { "product_attributes.resistance" : "Bludgeoning" } )

Wildcard indexes omit the _id field by default. To include the _id field in the wildcard index, you must explicitly include it in the wildcardProjection document (i.e. { "_id" : 1 }).

Create a Wildcard Index that Excludes Multiple Specific Field Paths

Consider a collection products_catalog where documents may contain a product_attributes field. The product_attributes field can contain arbitrary nested fields, including embedded documents and arrays:

{
  "_id" : ObjectId("5c1d358bf383fbee028aea0b"),
  "product_name" : "Blaster Gauntlet",
  "product_attributes" : {
     "price" : {
       "cost" : 299.99
       "currency" : USD
     }
     ...
  }
},
{
  "_id" : ObjectId("5c1d358bf383fbee028aea0c"),
  "product_name" : "Super Suit",
  "product_attributes" : {
     "superFlight" : true,
     "resistance" : [ "Bludgeoning", "Piercing", "Slashing" ]
     ...
  },
}

The following operation creates a wildcard index and uses the wildcardProjection document to index all scalar fields for each document in the collection, excluding the product_attributes.elements and product_attributes.resistance fields:

use inventory
db.runCommand(
  {
    createIndexes: "products_catalog",
    indexes: [
      {
        key: { "$**" : 1 },
        "wildcardProjection" : {
           "product_attributes.elements" : 0,
           "product_attributes.resistance" : 0
        },
        name: "wildcardIndex"
      }
    ]
  }
)

While the key pattern "$**" covers all fields in the document, the wildcardProjection field excludes the specified fields from the index.

If a field is a nested document or array, the wildcard index recurses into the document/array and indexes all scalar fields in the document/array.

The created index can support queries on any scalar field except those excluded by wildcardProjection:

db.products_catalog.find( { "product_attributes.maxSpeed" : { $gt: 25 } } )
db.products_catalog.find( { "product_attributes.superStrength" : true } )

As MongoDB includes an index on _id by default, wildcard indexes omit the _id field. To include the _id field in the wildcard index, you must explicitly include it in the wildcardProjection document:

"wildcardProjection" : {
  "_id" : 1
}

The _id field is the only field you can specify along with field exclusions.

Output

The createIndexes command returns a document that indicates the success of the operation. The document contains some but not all of the following fields, depending on outcome:

createIndexes.createdCollectionAutomatically

If true, then the collection didn’t exist and was created in the process of creating the index.

createIndexes.numIndexesBefore

The number of indexes at the start of the command.

createIndexes.numIndexesAfter

The number of indexes at the end of the command.

createIndexes.ok

A value of 1 indicates the indexes are in place. A value of 0 indicates an error.

createIndexes.note

This note is returned if an existing index or indexes already exist. This indicates that the index was not created or changed.

createIndexes.errmsg

Returns information about any errors.

createIndexes.code

The error code representing the type of error.

←   create currentOp  →