createIndexes

Definition

createIndexes

Builds one or more indexes on a collection.

Tip

In the mongo Shell, this command can also be run through the db.collection.createIndex() and db.collection.createIndexes() helper methods..

Helper methods are convenient for mongo users, but they may not return the same level of information as database commands. In cases where the convenience is not needed or the additional return fields are required, use the database command.

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> },
    commitQuorum: <int|string>,
    comment: <any>
  }
)

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.
commitQuorum	integer or string	Optional. The minimum number of data-bearing voting replica set members (i.e. commit quorum), including the primary, that must report a successful index build before the primary marks the indexes as ready. A "voting" member is any replica set member where members[n].votes is greater than 0. Supports the following values: "votingMembers" - all data-bearing voting replica set members (Default). "majority" - a simple majority of data-bearing voting replica set members. <int> - a specific number of data-bearing voting replica set members. 0 - Disables quorum-voting behavior. Members start the index build simultaneously but do not vote or wait for quorum before completing the index build. If you start an index build with a commit quorum of 0, you cannot later modify the commit quorum using setIndexCommitQuorum. A replica set tag name. New in version 4.4.
comment	any	Optional. A user-provided comment to attach to this command. Once set, this comment appears alongside records of this command in the following locations: mongod log messages, in the attr.command.cursor.comment field. Database profiler output, in the command.comment field. currentOp output, in the command.comment field. A comment can be any valid BSON type (string, integer, object, array, etc). New in version 4.4.

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

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: { "wildcardProjection" : { "_id" : 1, "<field>" : 0|1 } } With the exception of explicitly including _id field, you cannot combine inclusion and exclusion statements in the wildcardProjection document. 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.4 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: equality expressions (i.e. field: value or using the $eq operator), $exists: true expression, $gt, $gte, $lt, $lte expressions, $type expressions, $and operator at the top-level only You can specify a partialFilterExpression option for all MongoDB index types.
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. The following index types are sparse by default and ignore this option: 2dsphere 2d geoHaystack text 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. MongoDB provides the option to create partial indexes. These offer a superset of the functionality of sparse indexes and are preferred instead.
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.
hidden	boolean	Optional. A flag that determines whether the index is hidden from the query planner. A hidden index is not evaluated as part of query plan selection. Default is false. To use the hidden option, you must have featureCompatibilityVersion set to 4.4 or greater. However, once hidden, the index remains hidden even with featureCompatibilityVersion set to 4.2 on MongoDB 4.4 binaries. New in version 4.4.
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.
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.
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.
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. 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: { "wildcardProjection" : { "_id" : 1, "<field>" : 0|1 } } With the exception of explicitly including _id field, you cannot combine inclusion and exclusion statements in the wildcardProjection document.

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

Considerations

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

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

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

Behavior

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

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

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

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

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

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

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.

{
  "_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" ]
     ...
  },
}

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

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" } } )

{
  "_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" ]
     ...
  },
}

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

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

{
  "_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" ]
     ...
  },
}

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

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

{
  "_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" ]
     ...
  },
}

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

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

db.getSiblingDB("examples").runCommand(
  {
    createIndexes: "invoices",
    indexes: [
      {
        key: { "invoices" : 1 },
        "name" : "invoiceIndex"
      }
    ],
    "commitQuorum" : "majority"
  }
)

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.