Wildcard Indexes

Wildcard Indexes

MongoDB supports creating indexes on a field or set of fields tosupport queries. Since MongoDB supports dynamic schemas, applicationscan query against fields whose names cannot be known in advance or arearbitrary.

New in version MongoDB: 4.2

MongoDB 4.2 introduces wildcard indexes for supporting queriesagainst unknown or arbitrary fields.

Consider an application that captures user-defined data under theuserMetadata field and supports querying against that data:

{ "userMetadata" : { "likes" : [ "dogs", "cats" ] } }
{ "userMetadata" : { "dislikes" : "pickles" } }
{ "userMetadata" : { "age" : 45 } }
{ "userMetadata" : "inactive" }

Administrators want to create indexes to support queries on anysubfield of userMetadata.

A wildcard index on userMetadatacan support single-field queries on userMetadata,userMetadata.likes, userMetadata.dislikes, anduserMetadata.age:

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

The index can support the following queries:

db.userData.find({ "userMetadata.likes" : "dogs" })
db.userData.find({ "userMetadata.dislikes" : "pickles" })
db.userData.find({ "userMetadata.age" : { $gt : 30 } })
db.userData.find({ "userMetadata" : "inactive" })

A non-wildcard index on userMetadata can only support queries onvalues of userMetadata.

Important

Wildcard indexes are not designed to replace workload-based indexplanning. For more information on creating indexes to supportqueries, see Create Indexes to Support Your Queries. Forcomplete documentation on wildcard index limitations, seeWildcard Index Restrictions.

Create Wildcard Index

Important

The mongodfeatureCompatibilityVersion must be 4.2 tocreate wildcard indexes. For instructions on setting the fCV, seeSet Feature Compatibility Version on MongoDB 4.2 Deployments.

You can create wildcard indexes using thecreateIndexes database command or its shell helpers,createIndex() orcreateIndexes().

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 offieldA. If the field is a nested document or array, the wildcardindex recurses into the document/array and stores the value for allfields in the document/array.

For example, documents in the product_catalog collection may containa product_attributes field. The product_attributes field cancontain arbitrary nested fields, including embedded documents andarrays:

{
  "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 theproduct_attributes field:

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

The wildcard index can support arbitrary single-field queries onproduct_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" } )

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 documentin the collection. If a given field is a nested document or array, thewildcard index recurses into the document/array and stores the value forall fields in the document/array.

Important

Wildcard indexes omit the _id field by default. To include the_id field in the wildcard index, you must explicitly include itin the wildcardProjection document (i.e. { "_id" : 1 }).

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 specifiedfields for each document in the collection. If a given field is a nesteddocument or array, the wildcard index recurses into the document/arrayand stores the value for all fields in the document/array.

Important

Wildcard indexes omit the _id field by default. To include the_id field in the wildcard index, you must explicitly include itin the wildcardProjection document (i.e. { "_id" : 1 }).

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 documentexcluding specific field paths:

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

With this wildcard index, MongoDB indexes all fields for eachdocument in the collection excluding the specified field paths. If agiven field is a nested document or array, the wildcard index recursesinto the document/array and stores the values for all fields in thedocument/array.

Important

Wildcard indexes omit the _id field by default. To include the_id field in the wildcard index, you must explicitly include itin the wildcardProjection document (i.e. { "_id" : 1 }).

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

Considerations

Wildcard indexes can support at most one field in any given querypredicate. For more information on wildcard index querysupport, see Wildcard Index Query/Sort Support.
The mongodfeatureCompatibilityVersion must be 4.2 tocreate wildcard indexes. For instructions on setting the fCV, seeSet Feature Compatibility Version on MongoDB 4.2 Deployments.
Wildcard indexes omit the _id field by default. To include the _idfield in the wildcard index, you must explicitly include it in thewildcardProjection document (i.e. { "_id" : 1 }).
You can create multiple wildcard indexes in a collection.
A wildcard index may cover the same fields as other indexes in thecollection.
Wildcard indexes are Sparse Indexes and only containentries for documents that have the indexed field, even if the indexfield contains a null value.

Restrictions

You cannot shard a collection using a wildcard index. Create anon-wildcard index on the field or fields you want to shard on.For more information on shard key selection, seeShard Keys.
You cannot create a compound index.
You cannot specify the following properties for a wildcard index:
- TTL
- Unique
You cannot create the following index types using wildcard syntax:

Important

Wildcard Indexes are distinct from and incompatible withWildcard Text Indexes. Wildcard indexes cannot supportqueries using the $text operator.

For complete documentation on wildcard index creation restrictions, seeIncompatible Index Types or Properties.

Wildcard Index Query/Sort Support

Covered Queries

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

The query planner selects the wildcard index for satisfying thequery predicate.
The query predicate specifies exactly one field covered by the wildcardindex.
The projection explicitly excludes id and includes _only the queryfield.
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 fieldlastName and projects out all other fields from theresulting document:

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

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

Multi-Field Query Predicates

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

MongoDB cannot use a non-wildcard index to satisfy one part of aquery predicate and a wildcard index to satisfy another.
MongoDB cannot use one wildcard index to satisfy one part of a querypredicate 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 queryfields. All remaining fields are resolved without an index.

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

Queries with Sort

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

The query planner selects the wildcard index for satisfying thequery predicate.
The sort() specifies only the query predicatefield.
The specified field is never an array.

If the above conditions are not met, MongoDB cannot use the wildcardindex for the sort. MongoDB does not support sortoperations that require a different index from that of the querypredicate. 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 fieldproduct_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, MongoDBcan use the product_attributes.$** wildcard index for satisfyingboth the find() and sort().

Unsupported Query Patterns

Wildcard indexes cannot support query condition that checks if a fielddoes not exist.
Wildcard indexes cannot support query condition that checks if a fieldis or is not equal to a document or an array
Wildcard indexes cannot support query condition that checks if a fieldis not equal to null.

For details, see Unsupported Query and Aggregation Patterns.

Wildcard Index Restrictions