Navigation

Wildcard Indexes

MongoDB supports creating indexes on a field or set of fields to support queries. Since MongoDB supports dynamic schemas, applications can query against fields whose names cannot be known in advance or are arbitrary.

New in version MongoDB: 4.2

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

Consider an application that captures user-defined data under the userMetadata 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 any subfield of userMetadata.

A wildcard index on userMetadata can support single-field queries on userMetadata, userMetadata.likes, userMetadata.dislikes, and userMetadata.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 on values of userMetadata.

Important

Wildcard indexes are not designed to replace workload-based index planning. For more information on creating indexes to support queries, see Create Indexes to Support Your Queries. For complete documentation on wildcard index limitations, see Wildcard Index Restrictions.

Create Wildcard Index

Important

The mongod featureCompatibilityVersion must be 4.2 to create wildcard indexes. For instructions on setting the fCV, see Set Feature Compatibility Version on MongoDB 4.2 Deployments.

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

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

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.

Important

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

Important

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

Important

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 (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 query predicate. For more information on wildcard index query support, see Wildcard Index Query/Sort Support.
  • The mongod featureCompatibilityVersion must be 4.2 to create wildcard indexes. For instructions on setting the fCV, see Set Feature Compatibility Version on MongoDB 4.2 Deployments.
  • 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 (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 the collection.
  • Wildcard indexes are Sparse Indexes and only contain entries for documents that have the indexed field, even if the index field contains a null value.

Restrictions

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

Important

Wildcard Indexes are distinct from and incompatible with Wildcard Text Indexes. Wildcard indexes cannot support queries using the $text operator.

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

Wildcard Index Query/Sort Support

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 query condition that checks if a field does not exist.
  • Wildcard indexes cannot support query condition that checks if a field is or is not equal to a document or an array
  • Wildcard indexes cannot support query condition that checks if a field is not equal to null.

For details, see Unsupported Query and Aggregation Patterns.