createIndexes (original) (raw)

createIndexes

Builds one or more indexes on a collection.

Tip

In mongosh, this command can also be run through the db.collection.createIndex() anddb.collection.createIndexes() helper methods.

Helper methods are convenient for mongosh 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.

This command is available in deployments hosted in the following environments:

• MongoDB Atlas: The fully managed service for MongoDB deployments in the cloud

Note

This command is supported in all MongoDB Atlas clusters. For information on Atlas support for all commands, seeUnsupported Commands.

• MongoDB Enterprise: The subscription-based, self-managed version of MongoDB
• MongoDB Community: The source-available, free-to-use, and self-managed version of MongoDB

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:

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

mongosh provides the methodsdb.collection.createIndex() anddb.collection.createIndexes() as wrappers for thecreateIndexes command.

MongoDB disallows the creation of version 0 indexes.

The createIndexes command andmongosh helpersdb.collection.createIndex() anddb.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

}

Note

Requires featureCompatibilityVersion 4.4+

Each mongod in the replica set or sharded cluster_must_ have featureCompatibilityVersion set to at least 4.4 to start index builds simultaneously across replica set members.

Index builds on a replica set or sharded cluster build simultaneously across all data-bearing replica set members. For sharded clusters, the index build occurs only on shards containing data for the collection being indexed. The primary requires a minimum number of data-bearing voting members (i.e commit quorum), including itself, that must complete the build before marking the index as ready for use. See Index Builds in Replicated Environments for more information.

To start an index build with a non-default commit quorum, specify thecommitQuorum.

Use the setIndexCommitQuorum command to modify the commit quorum of an in-progress index build.

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

• Text indexes
• 2d indexes

Tip

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

When using Stable API V1:

• You cannot specify any of the following fields in the indexes array:
  • background
  • bucketSize
  • sparse
  • storageEngine
• You cannot create geoHaystack ortext indexes.
• The above unsupported index types are ignored by thequery planner instrict mode. For example, attempting to use a sparse index with cursor.hint()will result in the following BadValue error:

planner returned error :: caused by :: hint provided does not  
correspond to an existing index  

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

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

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

Important

If a data-bearing voting node becomes unreachable and thecommitQuorum is set to the default votingMembers, index builds can hang until that node comes back online.

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 200 megabytes, shared between all indexes built using a singlecreateIndexes command. Once the memory limit is reached,createIndexes uses temporary disk files in a subdirectory named _tmp within the --dbpathdirectory to complete the build.

You can override the memory limit by setting themaxIndexBuildMemoryUsageMegabytes server parameter. Setting a higher memory limit may result in faster completion of index builds. However, setting this limit too high relative to the unused RAM on your system can result in memory exhaustion and server shutdown.

The hidden option can be changed without dropping and recreating the index. SeeHidden Option.

Collation options on an existing index can be updated. To change other index options, drop the existing index withdb.collection.dropIndex() then run createIndexeswith the new 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 strengthof 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.

Warning

Because indexes that are configured with collation use ICU collation keys to achieve sort order, collation-aware index keys may be larger than index keys for indexes without 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 fieldcategory; 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" } )

Important

Matches against document keys, including embedded document keys, use simple binary comparison. This means that a query for a key like "foo.bár" will not match the key "foo.bar", regardless of the value you set for the strength parameter.

To change the hidden option for existing indexes, you can use the following mongosh methods:

• db.collection.hideIndex()
• db.collection.unhideIndex()

For example,

• To change the hidden option for an index to true, use thedb.collection.hideIndex() method:

db.restaurants.hideIndex( { borough: 1, ratings: 1 } );  

• To change the hidden option for an index to false, use thedb.collection.unhideIndex() method:

db.restaurants.unhideIndex( { borough: 1, city: 1 } );  

See also:

• 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  
  }  
}  

All of the statements in the wildcardProjection document must be either inclusion or exclusion statements. You can also include the_id field with exclusion statements. This is the only exception to the rule.

• Wildcard indexes do not support:
  • 2d (Geospatial) indexes
  • 2dsphere (Geospatial) indexes
  • Hashed indexes
  • Time to Live (TTL) indexes
  • Text indexes
  • Unique indexes
    Wildcard indexes are sparse indexes. They do not support queries when an indexed field does not exist. A wildcard index will index the document if the wildcard field has a nullvalue.
    Starting in MongoDB 7.0, wildcard indexes support ascending (1) and descending (-1) sort order. Earlier versions only supported ascending order.

To learn more, see:

• About Wildcard Indexes
• Wildcard Index Examples
• Wildcard Index Restrictions

You can create collections and indexes inside a distributed transaction if the transaction is not a cross-shard write transaction.

To use createIndexes in a transaction, the transaction must use read concern "local". If you specify a read concern level other than "local", the transaction fails.

See also:

There are important differences between commit quorums and write concerns:

• Index builds use commit quorums.
• Write operations use write concerns.

Each data-bearing node in a cluster is a voting member.

The commit quorum specifies how many data-bearing voting members, or which voting members, including the primary, must be prepared to commit a simultaneous index buildbefore the primary will execute the commit.

The write concern is the level of acknowledgment that the write has propagated to the specified number of instances.

Changed in version 8.0: The commit quorum specifies how many nodes must be ready to finish the index build before the primary commits the index build. In contrast, when the primary has committed the index build, the write concern specifies how many nodes must replicate the index build oplog entry before the command returns success.

In previous releases, when the primary committed the index build, the write concern specified how many nodes must finish the index build before the command returned success.

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.

The following lists examples of wildcard index creation:

• Create a Wildcard Index on a Single Field Path
• Create a Wildcard Index on All Field Paths
• Create a Wildcard Index on Multiple Specific Field Paths
• 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 can contain arbitrary nested fields, including embedded documents and arrays:

db.products_catalog.insertMany( [

  {

    _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 theproduct_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 ofproduct_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 onproduct_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" } } )

Note

The path-specific wildcard index syntax is incompatible with thewildcardProjection option. See the parameter documentation for more information.

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

db.products_catalog.insertMany( [

  {

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

Note

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. See parameter documentation for more information.

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

db.products_catalog.insertMany( [

  {

    _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 theproduct_attributes.elements and product_attributes.resistancefields 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, thewildcardProjection 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" } )

Note

Wildcard indexes do not support mixing inclusion and exclusion statements in the wildcardProjection document except when explicitly including the _id field. For more information onwildcardProjection, see the parameter documentation.

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

db.products_catalog.insertMany( [

  {

    _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 theproduct_attributes.elements and product_attributes.resistancefields:

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, thewildcardProjection 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 exceptthose excluded by wildcardProjection:

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

db.products_catalog.find( { "product_attributes.superStrength" : true } )

Note

Wildcard indexes do not support mixing inclusion and exclusion statements in the wildcardProjection document except when explicitly including the _id field. For more information onwildcardProjection, see the parameter documentation.

Note

Requires featureCompatibilityVersion 4.4+

Each mongod in the replica set or sharded cluster_must_ have featureCompatibilityVersion set to at least 4.4 to start index builds simultaneously across replica set members.

Index builds on a replica set or sharded cluster build simultaneously across all data-bearing replica set members. For sharded clusters, the index build occurs only on shards containing data for the collection being indexed. The primary requires a minimum number of data-bearing voting members (i.e commit quorum), including itself, that must complete the build before marking the index as ready for use. See Index Builds in Replicated Environments for more information.

To set the commit quorum, usecreateIndexes to specify the commitQuorum value.

commitQuorum specifies how many data-bearing voting members, or which voting members, including the primary, must be prepared to commit the index build before the primary will execute the commit. The default commit quorum is votingMembers, which means all data-bearing members.

The following operation creates an index with a commit quorum of "majority", or a simple majority of data-bearing members:

db.getSiblingDB("examples").runCommand(

  {

    createIndexes: "invoices",

    indexes: [

      {

        key: { "invoices" : 1 },

        "name" : "invoiceIndex"

      }

    ],

    "commitQuorum" : "majority"

  }

)

The primary marks index build as ready only after a simple majority of data-bearing voting members "vote" to commit the index build. For more information on index builds and the voting process, seeIndex Builds in Replicated Environments.

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 of0 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.

The following code block illustrates an example of the createIndexes output on a sharded cluster. On a sharded cluster, createIndexes outputs a raw embedded document which contains a document for each shard the index is built on. The keys of theraw embedded document are concantenations of shard id and the hostname and port of the individual nodes that make up the shard.

{

  raw: {

    'atlas-2m11gv-shard-1/atlas-2m11gv-shard-01-00.cpfgx.mongodb.net:27017,atlas-2m11gv-shard-01-01.cpfgx.mongodb.net:27017,atlas-2m11gv-shard-01-02.cpfgx.mongodb.net:27017': {

      numIndexesBefore: 3,

      numIndexesAfter: 5,

      createdCollectionAutomatically: false,

      commitQuorum: 'votingMembers',

      ok: 1

    },

    'atlas-2m11gv-shard-0/atlas-2m11gv-shard-00-00.cpfgx.mongodb.net:27017,atlas-2m11gv-shard-00-01.cpfgx.mongodb.net:27017,atlas-2m11gv-shard-00-02.cpfgx.mongodb.net:27017': {

      numIndexesBefore: 3,

      numIndexesAfter: 5,

      createdCollectionAutomatically: false,

      commitQuorum: 'votingMembers',

      ok: 1

    }

  },

  ok: 1,

  '$clusterTime': {

    clusterTime: Timestamp({ t: 1743624296, i: 7 }),

    signature: {

      hash: Binary.createFromBase64('22j0GK8SIK806T+0OdCY6qYHocM=', 0),

      keyId: Long('7438621020069560323')

    }

  },

  operationTime: Timestamp({ t: 1743624296, i: 7 })

}