db.collection.createIndex() (original) (raw)

db.collection.createIndex(keys, options, commitQuorum)

Creates indexes on collections.

This method 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 createIndex() method has the following form:

db.collection.createIndex( <keys>, <options>, <commitQuorum>)

The createIndex() method takes the following parameters:

The options document contains a set of options that controls the creation of the index. Different index types can have additional options specific for that type.

Multiple index options can be specified in the same document. However, if you specify multiple option documents the db.collection.createIndex()operation will fail.

Consider the following db.collection.createIndex() operation:

db.collection.createIndex(

  {

      "a": 1

  },

  {

      unique: true,

      sparse: true,

      expireAfterSeconds: 3600

  }

)

If the options specification had been split into multiple documents like this:{ unique: true }, { sparse: true, expireAfterSeconds: 3600 }the index creation operation would have failed.

The following options are available for all index types unless otherwise specified:

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.

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.

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.

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.

The following options are available for textindexes only:

The following option is available for 2dsphereindexes only:

The following options are available for 2d indexes only:

Wildcard indexes can use thewildcardProjection option.

If you call db.collection.createIndex() for an index that already exists, MongoDB does not recreate the index.

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.

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

To change the other index options, drop the existing index withdb.collection.dropIndex() before runningdb.collection.createIndex() with 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.

To hide or unhide existing indexes, you can use the followingmongosh 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:

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

To use db.collection.createIndex() 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:

Changed in version 7.1.

Starting in MongoDB 7.1, index builds are improved with faster error reporting and increased failure resilience. You can also set the minimum available disk space required for index builds using the newindexBuildMinAvailableDiskSpaceMB parameter, which stops index builds if disk space is too low.

The following table compares the index build behavior starting in MongoDB 7.1 with earlier versions.

The following example creates an ascending index on the fieldorderDate.

db.collection.createIndex( { orderDate: 1 } )

If the keys document specifies more than one field, thencreateIndex() creates a compound index.

The following example creates a compound index on theorderDate field (in ascending order) and the zipcodefield (in descending order.)

db.collection.createIndex( { orderDate: 1, zipcode: -1 } )

Compound indexes can include a single hashed field. Compound hashed indexes require featureCompatibilityVersionset to at least 5.0.

The following example creates a compound index on the state field (in ascending order) and the zipcode field (hashed):

db.collection.createIndex( { "state" : 1, "zipcode" : "hashed" } )

The order of fields in a compound index is important for supportingsort() operations using the index.

See also:

• Sort on Multiple Fields
• Sort and Index Prefix

The following example creates an index named category_fr. The example creates the index with the collation that specifies the locale fr and comparison strength 2:

db.collection.createIndex(

   { category: 1 },

   { name: "category_fr", collation: { locale: "fr", strength: 2 } }

)

The following example creates a compound index nameddate_category_fr with a collation. The collation applies only to the index keys with string values.

db.collection.createIndex(

   { orderDate: 1, category: 1 },

   { name: "date_category_fr", collation: { locale: "fr", strength: 2 } }

)

The collation applies to the indexed keys whose values are string.

For queries or sort operations on the indexed keys that uses the same collation rules, MongoDB can use the index. For details, see Collation and Index Use.

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

For examples, see:

• Create a Wildcard Index on a Single Field Path
• Create a Wildcard Index on All Field Paths
• Include Specific Fields in Wildcard Index Coverage
• Omit Specific Fields from Wildcard Index Coverage

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.products_catalog.createIndex( { "product_attributes.$**" : 1 } )

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.products_catalog.createIndex( { "$**" : 1 } )

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.products_catalog.createIndex(

  { "$**" : 1 },

  {

    "wildcardProjection" : {

      "product_attributes.elements" : 1,

      "product_attributes.resistance" : 1

    }

  }

)

The pattern "$**" includes all fields in the document. Use thewildcardProjection field to limit the index to fields you specify. For complete documentation on wildcardProjection, seeOptions for wildcard indexes.

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

The wildcard index supports 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" ]

    }

  }

] )

This example uses a wildcard index and a wildcardProjectiondocument to index the scalar fields for each document in the collection.

The wildcard index excludes the product_attributes.elements andproduct_attributes.resistance fields:

use inventory

db.products_catalog.createIndex(

  { "$**" : 1 },

  {

    "wildcardProjection" : {

      "product_attributes.elements" : 0,

      "product_attributes.resistance" : 0

    }

  }

)

The wildcard pattern "$**" includes all of the fields in the document. However, the wildcardProjection field excludes the specified fields from the index.

For complete documentation on wildcardProjection, seeOptions for wildcard indexes.

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

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, usecreateIndex() 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 voting members:

db.getSiblingDB("examples").invoices.createIndex(

  { "invoices" : 1 },

  { },

  "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 Indexes section of this manual for full documentation of indexes and indexing in MongoDB.
• db.collection.getIndexes() to view the specifications of existing indexes for a collection.
• Text Indexes on Self-Managed Deployments for details on creating textindexes.
• Geospatial Indexes for geospatial queries.
• TTL Indexes for expiration of data.