Wildcard Indexes

Create a Wildcard Index on a Field

To index the value of a specific field:

db.collection.createIndex( { "fieldA.$**" : 1 } )

With this wildcard index, MongoDB indexes all values of fieldA. If the field is a nested document or array, the wildcard index recurses into the document/array and stores the value for all fields in the document/array.

For example, documents in the product_catalog collection may contain a product_attributes field. The product_attributes field can contain arbitrary nested fields, including embedded documents and arrays:

{
  "product_name" : "Spy Coat",
  "product_attributes" : {
    "material" : [ "Tweed", "Wool", "Leather" ]
    "size" : {
      "length" : 72,
      "units" : "inches"
    }
  }
}
{
  "product_name" : "Spy Pen",
  "product_attributes" : {
     "colors" : [ "Blue", "Black" ],
     "secret_feature" : {
       "name" : "laser",
       "power" : "1000",
       "units" : "watts",
     }
  }
}

The following operation creates a wildcard index on the product_attributes field:

db.products_catalog.createIndex( { "product_attributes.$**" : 1 } )

The wildcard index can support arbitrary single-field queries on product_attributes or its embedded fields:

db.products_catalog.find( { "product_attributes.size.length" : { $gt : 60 } } )
db.products_catalog.find( { "product_attributes.material" : "Leather" } )
db.products_catalog.find( { "product_attributes.secret_feature.name" : "laser" } )

For an example, see Create a Wildcard Index on a Single Field Path.

Create a Wildcard Index on All Fields

To index the value of all fields in a document (excluding _id), specify "$**" as the index key:

db.collection.createIndex( { "$**" : 1 } )

With this wildcard index, MongoDB indexes all 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 stores the value for all fields in the document/array.

For an example, see Create a Wildcard Index on All Field Paths.

Create a Wildcard Index on Multiple Specific Fields

To index the values of multiple specific fields in a document:

db.collection.createIndex(
  { "$**" : 1 },
  { "wildcardProjection" :
    { "fieldA" : 1, "fieldB.fieldC" : 1 }
  }
)

With this wildcard index, MongoDB indexes all values for the specified 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 stores the value for all fields in the document/array.

For an example, see Include Specific Fields in Wildcard Index Coverage.

Create a Wildcard Index that Excludes Multiple Specific Fields

To index the fields of all fields in a document excluding specific field paths:

db.collection.createIndex(
  { "$**" : 1 },
  { "wildcardProjection" :
    { "fieldA" : 0, "fieldB.fieldC" : 0 }
  }
)

With this wildcard index, MongoDB indexes all fields for each document in the collection excluding the specified field paths. If a given field is a nested document or array, the wildcard index recurses into the document/array and stores the values for all fields in the document/array.

For an example, see Omit Specific Fields from Wildcard Index Coverage.

Viewing Normalized wildcard Indexes

Starting in MongoDB 5.0, wildcard indexes are normalized after creation, multiple wildcard indexes can be created using the same key pattern as long as the wildcardProjection fields do not express equivalent filters.

For example, create a wildcard index:

db.books.createIndex( { "$**" : 1 }, { wildcardProjection : {a: 1, "b.c": 1 } } )

View the index with the db.collection.getIndexes() method:

db.books.getIndexes()

The results are displayed in a normalized format:

   { v: 2, key: { _id: 1 }, name: '_id_' },
   {
     v: 2,
     key: { '$**': 1 },
     name: '$**_1',
     wildcardProjection: { a: true, b: { c: true }, _id: false }
   }

Nested Objects

When a wildcard index encounters a nested object, it descends into the object and indexes its contents. For example:

{
  "parentField" : {
    "nestedField" : "nestedValue",
    "nestedArray" : ["nestedElement"]
    "nestedObject" : {
      "deeplyNestedField" : "deeplyNestedValue"
    }
  }
}

A wildcard index which includes parentField descends into the object to traverse and index its contents:

• For each field which is itself an object (i.e. an embedded document), descend into the object to index its contents.

• For each field which is an array, traverse the array and index its contents.

• For all other fields, record the primitive (non-object/array) value into the index.

The wildcard index continues traversing any additional nested objects or arrays until it reaches a primitive value (i.e. a field that is not an object or array). It then indexes this primitive value, along with the full path to that field.

Given the sample document, the wildcard index adds the following records to the index:

• "parentField.nestedField" : "nestedValue"

• "parentField.nestedObject.deeplyNestedField" : "deeplyNestedValue"

• "parentField.nestedArray" : "nestedElement"

For more information on wildcard index behavior with nested arrays, see Nested Arrays.

Nested Arrays

When a wildcard index encounters a nested array, it attempts to traverse the array to index its elements. If the array is itself an element in a parent array (i.e. an embedded array), the wildcard index instead records the entire array as a value instead of traversing its contents. For example:

{
  "parentArray" : [
    "arrayElementOne",
    [ "embeddedArrayElement" ],
    "nestedObject" : {
      "nestedArray" : [
        "nestedArrayElementOne",
        "nestedArrayElementTwo"
      ]
    }
  ]
}

A wildcard index which includes parentArray descends into the array to traverse and index its contents:

• For each element which is an array (i.e. an embedded array), index the entire array as a value.

• For each element which is an object, descend into the object to traverse and index its contents.

• For all other fields, record the primitive (non-object/array) value into the index.

The wildcard index continues traversing any additional nested objects or arrays until it reaches a primitive value (i.e. a field that is not an object or array). It then indexes this primitive value, along with the full path to that field.

Given the sample document, the wildcard index adds the following records to the index:

• "parentArray" : "arrayElementOne"

• "parentArray" : ["embeddedArrayElement"]

• "parentArray.nestedObject.nestedArray" : "nestedArrayElementOne"

• "parentArray.nestedObject.nestedArray" : "nestedArrayElementTwo"

Note that the records for parentField.nestedArray do not include the array position for each element. Wildcard indexes ignore array element positions when recording the element into the index. Wildcard indexes can still support queries that include explicit array indices. See Queries with Explicit Array Indices for more information.

Index Display

Starting in MongoDB 5.0.16, the wildcardProjection field stores the index projection in its submitted form. Earlier versions of the server may have stored the projection in a normalized form.

The server uses the index the same way, but you may notice a difference in the output of the listIndexes and db.collection.getIndexes() commands.

Covered Queries

Wildcard indexes can support a covered query only if all of the following are true:

• The query planner selects the wildcard index for satisfying the query predicate.

• The query predicate specifies exactly one field covered by the wildcard index.

• The projection explicitly excludes _id and includes only the query field.

• The specified query field is never an array.

Consider the following wildcard index on the employees collection:

db.products.createIndex( { "$**" : 1 } )

The following operation queries for a single field lastName and projects out all other fields from the resulting document:

db.products.find(
  { "lastName" : "Doe" },
  { "_id" : 0, "lastName" : 1 }
)

Assuming that the specified lastName is never an array, MongoDB can use the $** wildcard index for supporting a covered query.

Multi-Field Query Predicates

Wildcard indexes can support at most one query predicate field. That is:

• MongoDB cannot use a non-wildcard index to satisfy one part of a query predicate and a wildcard index to satisfy another.

• MongoDB cannot use one wildcard index to satisfy one part of a query predicate and another wildcard index to satisfy another.

• Even if a single wildcard index could support multiple query fields, MongoDB can use the wildcard index to support only one of the query fields. All remaining fields are resolved without an index.

However, MongoDB may use the same wildcard index for satisfying each independent argument of the query $or or aggregation $or operators.

Queries with Sort

MongoDB can use a wildcard index for satisfying the sort() only if all of the following are true:

• The query planner selects the wildcard index for satisfying the query predicate.

• The sort() specifies only the query predicate field.

• The specified field is never an array.

If the above conditions are not met, MongoDB cannot use the wildcard index for the sort. MongoDB does not support sort() operations that require a different index from that of the query predicate. For more information, see Index Intersection and Sort.

Consider the following wildcard index on the products collection:

db.products.createIndex( { "product_attributes.$**" : 1 } )

The following operation queries for a single field product_attributes.price and sorts on that same field:

db.products.find(
  { "product_attributes.price" : { $gt : 10.00 } },
).sort(
  { "product_attributes.price" : 1 }
)

Assuming that the specified price is never an array, MongoDB can use the product_attributes.$** wildcard index for satisfying both the find() and sort().

Unsupported Query Patterns

Wildcard indexes cannot support the following query patterns:

• Queries that check if a field does not exist

• Queries that check if a field is or is not equal to a document or an array

• Queries that check if a field is equal to null

For details, see Unsupported Query and Aggregation Patterns.

Queries with Explicit Array Indices

MongoDB wildcard indexes do not record the array position of any given element in an array during indexing. However, MongoDB may still select the wildcard index to answer a query which includes a field path with one or more explicit array indices (for example, parentArray.0.nestedArray.0). Due to the increasing complexity of defining index bounds for each consecutive nested array, MongoDB does not consider the wildcard index to answer a given field path in the query if that path contains more than 8 explicit array indices. MongoDB can still consider the wildcard index to answer other field paths in the query.

For example:

{
  "parentObject" : {
    "nestedArray" : [
       "elementOne",
       {
         "deeplyNestedArray" : [ "elementTwo" ]
       }
     ]
  }
}

MongoDB can select a wildcard index which includes parentObject to satisfy the following queries:

• "parentObject.nestedArray.0" : "elementOne"

• "parentObject.nestedArray.1.deeplyNestedArray.0" : "elementTwo"

If a given field path in the query predicate specifies more than 8 explicit array indices, MongoDB does not consider the wildcard index for answering that field path. MongoDB instead either selects another eligible index to answer the query, or performs a collection scan.

Note that wildcard indexes themselves do not have any limits on the depth to which they traverse a document while indexing it; the limitation only applies to queries which explicitly specify exact array indices. By issuing the same queries without the explicit array indices, MongoDB may select the wildcard index to answer the query:

• "parentObject.nestedArray" : "elementOne"

• "parentObject.nestedArray.deeplyNestedArray" : "elementTwo"