createIndexes (database command)

Non-Hidden Option

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

Changing Index Options

Collation options on an existing index can be updated. To change other index options, drop the existing index with db.collection.dropIndex() then run createIndexes with the new options.

Collation Option

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.

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.

A restaurants collection has the following documents:

db.restaurants.insertMany( [
   { _id: 1, category: "café", status: "Open" },
   { _id: 2, category: "cafe", status: "open" },
   { _id: 3, category: "cafE", status: "open" }
] )

The restaurants collection has an index on a string field category with the collation locale "fr".

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

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

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

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

db.restaurants.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 restaurants 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.restaurants.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.restaurants.find( { score: 5 } ).sort( { price: 1 } )
db.restaurants.find( { score: 5, price: { $gt: Decimal128( "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.restaurants.find( { score: 5, category: "cafe" } )

To confirm whether a query used an index, run the query with the explain() option.

Hidden Option

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 the db.collection.hideIndex() method:

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

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

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

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:

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

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

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:

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

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:

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