createIndexes

Definition

  • createIndexes
  • Builds one or more indexes on a collection.

The createIndexes command takes the following form:

  1. db.runCommand(
  2.   {
  3.     createIndexes: <collection>,
  4.     indexes: [
  5.         {
  6.             key: {
  7.                 <key-value_pair>,
  8.                 <key-value_pair>,
  9.                 ...
  10.             },
  11.             name: <index_name>,
  12.             <option1>,
  13.             <option2>,
  14.             ...
  15.         },
  16.         { ... },
  17.         { ... }
  18.     ],
  19.     writeConcern: { <write concern> }
  20.   }
  21. )

The createIndexes command takes the following fields:

FieldTypeDescriptioncreateIndexesstringThe collection for which to create indexes.indexesarraySpecifies the indexes to create. Each document in the array specifies aseparate index.writeConcerndocumentOptional. 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.

FieldTypeDescriptionkeydocumentSpecifies the index’s fields. For each field, specify a key-value pairin which the key is the name of the field to index and the value iseither the index direction or index type. Ifspecifying direction, specify 1 for ascending or -1 fordescending.

MongoDB supports several different index types includingtext, geospatial, and hashed indexes. See index typesfor more information.

Changed in version 4.2: MongoDB 4.2 wildcard indexessupport workloads where users query against custom fields or alarge variety of fields in a collection:

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

You can also either include or exclude specific fields andtheir subfields from the index using the optionalwildcardProjection parameter.

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

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

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

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

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

The wildcard index key must use one of the syntaxes listedabove. For example, you cannot specify acompound index key. For morecomplete documentation on wildcard indexes, including restrictionson their creation, see Wildcard Index Restrictions.

The mongodfeatureCompatibilityVersion must be 4.2 tocreate wildcard indexes. For instructions on setting the fCV, seeSet Feature Compatibility Version on MongoDB 4.2 Deployments.

For examples of wildcard index creation, seeCreate a Wildcard Index.namestringA name that uniquely identifies the index.backgroundbooleanOptional. Deprecated in MongoDB 4.2.

  • For feature compatibility version (fcv) "4.0",specifying background: true directs MongoDB to build theindex in the background. Background builds donot block operations on the collection. The default value isfalse.

Changed in version 4.2.

For feature compatibility version (fcv) "4.2",all index builds use an optimizedbuild process that holds the exclusivelock only at the beginning and end of the build process. The rest ofthe build process yields to interleaving read and write operations.MongoDB ignores the background option if specified.uniquebooleanOptional. Creates a unique index so that the collection will not acceptinsertion or update of documents where the index key value matches an existingvalue in the index.

Specify true to create a unique index. Thedefault value is false.

The option is unavailable for hashedindexes.partialFilterExpressiondocumentOptional. If specified, the index only references documents that match thefilter expression. See Partial Indexes for more information.

A filter expression can include:

  • equality expressions (i.e. field: value or using the $eqoperator),
  • $exists: true expression,
  • $gt, $gte, $lt, $lte expressions,
  • $type expressions,
  • $and operator at the top-level onlyYou can specify a partialFilterExpression option for all MongoDBindex types.

New in version 3.2.

sparsebooleanOptional. If true, the index only references documents with the specifiedfield. These indexes use less space but behave differently in somesituations (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 createpartial indexes. Partial indexesoffer a superset of the functionality of sparse indexes. If youare 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 andignore this option. For a compound index that includes2dsphere index key(s) along with keys of other types, only the2dsphere index fields determine whether the index references adocument.

2d, geoHaystack, andtext indexes behave similarly to the2dsphere indexes.

expireAfterSecondsintegerOptional. Specifies a value, in seconds, as a TTL to control how longMongoDB retains documents in this collection. SeeExpire Data from Collections by Setting TTL for more information on thisfunctionality. This applies only to TTL indexes.storageEnginedocumentOptional. Allows users to configure the storage engine on aper-index basis when creating an index.

The storageEngine option should take the following form:

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

Storage engine configuration options specified when creating indexes arevalidated and logged to the oplog during replication tosupport replica sets with members that use different storageengines.

New in version 3.0.

weightsdocumentOptional. For text indexes, a document that containsfield and weight pairs. The weight is an integer ranging from 1 to99,999 and denotes the significance of the field relative to theother indexed fields in terms of the score. You can specify weightsfor some or all the indexed fields. SeeControl Search Results with Weights to adjust the scores.The default value is 1.default_languagestringOptional. For text indexes, the language thatdetermines the list of stop words and the rules for the stemmer andtokenizer. See Text Search Languages for the availablelanguages and Specify a Language for Text Index formore information and examples. The default value is english.language_overridestringOptional. For text indexes, the name of the field, inthe collection’s documents, that contains the override language forthe document. The default value is language. SeeUse any Field to Specify the Language for a Document for an example.textIndexVersionintegerOptional. The text index version number. Users can use this option tooverride the default version number.

For available versions, see Versions.

New in version 2.6.

2dsphereIndexVersionintegerOptional. The 2dsphere index version number. Users can use this option tooverride the default version number.

For the available versions, see Versions.

New in version 2.6.

bitsintegerOptional. For 2d indexes, the number of precision of thestored geohash value of the location data.

The bits value ranges from 1 to 32 inclusive. The default valueis 26.minnumberOptional. For 2d indexes, the lower inclusive boundary forthe longitude and latitude values. The default value is -180.0.maxnumberOptional. For 2d indexes, the upper inclusive boundary forthe longitude and latitude values. The default value is 180.0.bucketSizenumberFor geoHaystack indexes, specify thenumber of units within which to group the location values; i.e. groupin the same bucket those location values that are within thespecified number of units to each other.

The value must be greater than 0.collationdocumentOptional. Specifies the collation for the index.

Collation allows users to specifylanguage-specific rules for string comparison, such as rules forlettercase 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, MongoDBcreates the index with the collection’s default collation.
  • If you do specify a collation when creating the index, MongoDBcreates the index with the specified collation.The collation option has the following syntax:
  1. collation: {
  2.    locale: <string>,
  3.    caseLevel: <boolean>,
  4.    caseFirst: <string>,
  5.    strength: <int>,
  6.    numericOrdering: <boolean>,
  7.    alternate: <string>,
  8.    maxVariable: <string>,
  9.    backwards: <boolean>
  10. }

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

New in version 3.4.

wildcardProjectiondocumentOptional. Allows users to include or exclude specific field paths froma wildcard index using the{ "$" : 1} key pattern. This option is onlyvalid if creating a wildcard index onall document fields. You cannot specify this option if creatinga wildcard index on a specific field path and its subfields, e.g.{ "path.to.field.$" : 1 }

The wildcardProjection option takes the following form:

  1. wildcardProjection: {
  2.   "path.to.field.a" : <value>,
  3.   "path.to.field.b" : <value>
  4. }

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 excludespecifications, with the exception of including the _id fieldwhile excluding other fields.

Note

As MongoDB includes an index on _id by default, wildcardindexes omit the _id field. To include the _id field in thewildcard index, you must explicitly include it in thewildcardProjection document:

  1. "wildcardProjection" : {
  2.   "_id" : 1
  3. }

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

The mongo shell provides the methodsdb.collection.createIndex() anddb.collection.createIndexes() as wrappers for thecreateIndexes command.

Considerations

Changed in version 3.2: MongoDB disallows the creation of version 0 indexes. To upgrade existing version 0indexes, 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 theIndex Name Length limit of 127 byte maximum. In previousversions or MongoDB versions withfeatureCompatibilityVersion (fCV) set to"4.0", index names must fall within thelimit.

Starting in version 4.2, the createIndexes command andthe mongo shell helpersdb.collection.createIndex() anddb.collection.createIndexes() report an error if youcreate an index with one name, and then try to create the same indexagain but with another name.

  1. {
  2.    "ok" : 0,
  3.    "errmsg" : "Index with name: x_1 already exists with a different name",
  4.    "code" : 85,
  5.    "codeName" : "IndexOptionsConflict"
  6. }

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

  1. {
  2.    "numIndexesBefore" : 2,
  3.    "numIndexesAfter" : 2,
  4.    "note" : "all indexes already exist",
  5.    "ok" : 1
  6. }

Replica Sets and Sharded Clusters

To minimize the impact of building an index on replica sets and shardedclusters, use a rolling index build procedureas described on Build Indexes on Replica Sets.

Collation and Index Types

The following indexes only support simple binary comparison and donot support collation:

Tip

To create a text, a 2d, or a geoHaystack index on acollection that has a non-simple collation, you must explicitlyspecify {collation: {locale: "simple"} } when creating theindex.

Behavior

Concurrency

Changed in version 4.2.

For featureCompatibilityVersion "4.2", createIndexesuses an optimized build process that obtains and holds an exclusive lock onthe specified collection at the start and end of the index build. Allsubsequent operations on the collection must wait until createIndexes releasesthe exclusive lock. createIndexes allows interleaving read and writeoperations during the majority of the index build.

For featureCompatibilityVersion "4.0", createIndexesuses the pre-4.2 index build process which by default obtains an exclusivelock on the parent database for the entire duration of the build process. Thepre-4.2 build process blocks all operations on the database and all itscollections until the operation completed. background indexes do not takean exclusive lock.

For more information on the locking behavior of createIndexes, seeIndex Builds on Populated Collections.

Memory Usage Limit

Changed in version 3.4: createIndexes supports building one or more indexes on acollection. createIndexes uses a combination of memory andtemporary files on disk to complete index builds. The default limit onmemory usage for createIndexes is 500 megabytes, sharedbetween all indexes built using a single createIndexescommand. Once the memory limit is reached, createIndexesuses temporary disk files in a subdirectory named _tmp within the—dbpath directory to complete the build.

You can override the memory limit by setting themaxIndexBuildMemoryUsageMegabytes server parameter. Settinga higher memory limit may result in faster completion of index buildslarger than 500 megabytes. However, setting this limit too high relativeto 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 setof index options and then try to recreate the same index but withdifferent index options, MongoDB will not change the options norrecreate the index.

To change these index options, drop the existing index withdb.collection.dropIndex() before runningcreateIndexes with the new options.

Collation Option

New in version 3.4.

Unlike other index options, you can create multiple indexes on the samekey(s) with different collations. To create indexes with the same keypattern 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, MongoDBcreates the index with the collection’s default collation.
  • If you do specify a collation when creating the index, MongoDBcreates the index with the specified collation.

Tip

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

To use an index for string comparisons, an operation must alsospecify the same collation. That is, an index with a collationcannot support an operation that performs string comparisons on theindexed fields if the operation specifies a different collation.

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

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

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

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

  1. 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 adifferent collation can still use the index to support comparisonson the index prefix keys.

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

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

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

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

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

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

Wildcard Indexes

New in version 4.2.

Note

Wildcard Indexes are distinct from and incompatible withWildcard Text Indexes. Wildcard indexes cannot supportqueries using the $text operator.

For complete documentation on wildcard index restrictions, seeWildcard Index Restrictions.

For examples of wildcard index creation, seeCreate a Wildcard Index. For completedocumentation on Wildcard Indexes, see Wildcard Indexes.

Example

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

  1. db.getSiblingDB("products").runCommand(
  2.   {
  3.     createIndexes: "inventory",
  4.     indexes: [
  5.         {
  6.             key: {
  7.                 item: 1,
  8.                 manufacturer: 1,
  9.                 model: 1
  10.             },
  11.             name: "item_manufacturer_model",
  12.             unique: true
  13.         },
  14.         {
  15.             key: {
  16.                 item: 1,
  17.                 supplier: 1,
  18.                 model: 1
  19.             },
  20.             name: "item_supplier_model",
  21.             unique: true
  22.         }
  23.     ],
  24.     writeConcern: { w: "majority" }
  25.   }
  26. )

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

Create a Wildcard Index

New in version 4.2: The mongodfeatureCompatibilityVersion must be 4.2 tocreate wildcard indexes. For instructions on setting the fCV, seeSet Feature Compatibility Version on MongoDB 4.2 Deployments.

For complete documentation on Wildcard Indexes, seeWildcard 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 aproduct_attributes field. The product_attributes field cancontain arbitrary nested fields, including embeddeddocuments and arrays:

  1. {
  2.   "_id" : ObjectId("5c1d358bf383fbee028aea0b"),
  3.   "product_name" : "Blaster Gauntlet",
  4.   "product_attributes" : {
  5.      "price" : {
  6.        "cost" : 299.99
  7.        "currency" : USD
  8.      }
  9.      ...
  10.   }
  11. },
  12. {
  13.   "_id" : ObjectId("5c1d358bf383fbee028aea0c"),
  14.   "product_name" : "Super Suit",
  15.   "product_attributes" : {
  16.      "superFlight" : true,
  17.      "resistance" : [ "Bludgeoning", "Piercing", "Slashing" ]
  18.      ...
  19.   },
  20. }

The following operation creates a wildcard index on theproduct_attributes field:

  1. use inventory
  2. db.runCommand(
  3.   {
  4.     createIndexes: "products_catalog",
  5.     indexes: [
  6.       {
  7.         key: { "product_attributes.$**" : 1 },
  8.         name: "wildcardIndex"
  9.       }
  10.     ]
  11.   }
  12. )

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

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

  1. db.products_catalog.find( { "product_attributes.superFlight" : true } )
  2. db.products_catalog.find( { "product_attributes.maxSpeed" : { $gt : 20 } } )
  3. 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 aproduct_attributes field. The product_attributes field cancontain arbitrary nested fields, including embeddeddocuments and arrays:

  1. {
  2.   "_id" : ObjectId("5c1d358bf383fbee028aea0b"),
  3.   "product_name" : "Blaster Gauntlet",
  4.   "product_attributes" : {
  5.      "price" : {
  6.        "cost" : 299.99
  7.        "currency" : USD
  8.      }
  9.      ...
  10.   }
  11. },
  12. {
  13.   "_id" : ObjectId("5c1d358bf383fbee028aea0c"),
  14.   "product_name" : "Super Suit",
  15.   "product_attributes" : {
  16.      "superFlight" : true,
  17.      "resistance" : [ "Bludgeoning", "Piercing", "Slashing" ]
  18.      ...
  19.   },
  20. }

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

  1. use inventory
  2. db.runCommand(
  3.   {
  4.     createIndexes: "products_catalog",
  5.     indexes: [
  6.       {
  7.         key: { "$**" : 1 },
  8.         name: "wildcardIndex"
  9.       }
  10.     ]
  11.   }
  12. )

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

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

  1. db.products_catalog.find( { "product_price" : { $lt : 25 } } )
  2. 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 itin 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 aproduct_attributes field. The product_attributes field cancontain arbitrary nested fields, including embeddeddocuments and arrays:

  1. {
  2.   "_id" : ObjectId("5c1d358bf383fbee028aea0b"),
  3.   "product_name" : "Blaster Gauntlet",
  4.   "product_attributes" : {
  5.      "price" : {
  6.        "cost" : 299.99
  7.        "currency" : USD
  8.      }
  9.      ...
  10.   }
  11. },
  12. {
  13.   "_id" : ObjectId("5c1d358bf383fbee028aea0c"),
  14.   "product_name" : "Super Suit",
  15.   "product_attributes" : {
  16.      "superFlight" : true,
  17.      "resistance" : [ "Bludgeoning", "Piercing", "Slashing" ]
  18.      ...
  19.   },
  20. }

The following operation creates a wildcard index and usesthe wildcardProjection option to include only scalar values of theproduct_attributes.elements and product_attributes.resistancefields in the index.

  1. use inventory
  2. db.runCommand(
  3.   {
  4.     createIndexes: "products_catalog",
  5.     indexes: [
  6.       {
  7.         key: { "$**" : 1 },
  8.         "wildcardProjection" : {
  9.           "product_attributes.elements" : 1,
  10.           "product_attributes.resistance" : 1
  11.         },
  12.         name: "wildcardIndex"
  13.       }
  14.     ]
  15.   }
  16. )

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

If a field is a nested document or array, the wildcardindex recurses into the document/array and indexes all scalar fields inthe document/array.

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

  1. db.products_catalog.find( { "product_attributes.elements" : { $eq: "Water" } } )
  2. 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 itin 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 aproduct_attributes field. The product_attributes field cancontain arbitrary nested fields, including embeddeddocuments and arrays:

  1. {
  2.   "_id" : ObjectId("5c1d358bf383fbee028aea0b"),
  3.   "product_name" : "Blaster Gauntlet",
  4.   "product_attributes" : {
  5.      "price" : {
  6.        "cost" : 299.99
  7.        "currency" : USD
  8.      }
  9.      ...
  10.   }
  11. },
  12. {
  13.   "_id" : ObjectId("5c1d358bf383fbee028aea0c"),
  14.   "product_name" : "Super Suit",
  15.   "product_attributes" : {
  16.      "superFlight" : true,
  17.      "resistance" : [ "Bludgeoning", "Piercing", "Slashing" ]
  18.      ...
  19.   },
  20. }

The following operation creates a wildcard index and usesthe wildcardProjection document to index all scalar fieldsfor each document in the collection, excluding theproduct_attributes.elements and product_attributes.resistancefields:

  1. use inventory
  2. db.runCommand(
  3.   {
  4.     createIndexes: "products_catalog",
  5.     indexes: [
  6.       {
  7.         key: { "$**" : 1 },
  8.         "wildcardProjection" : {
  9.            "product_attributes.elements" : 0,
  10.            "product_attributes.resistance" : 0
  11.         },
  12.         name: "wildcardIndex"
  13.       }
  14.     ]
  15.   }
  16. )

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

If a field is a nested document or array, the wildcardindex recurses into the document/array and indexes all scalar fields inthe document/array.

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

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

As MongoDB includes an index on _id by default, wildcardindexes omit the _id field. To include the _id field in thewildcard index, you must explicitly include it in thewildcardProjection document:

  1. "wildcardProjection" : {
  2.   "_id" : 1
  3. }

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

Output

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

  • createIndexes.createdCollectionAutomatically
  • If true, then the collection didn’t exist and was created in theprocess 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 of0 indicates an error.
  • createIndexes.note
  • This note is returned if an existing index or indexes alreadyexist. 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.