Docs Menu
Docs Home
/
MongoDB Manual
/ / /

Include or Exclude Fields in a Wildcard Index

On this page

  • Restrictions
  • Before You Begin
  • Procedures
  • Include Specific Fields in a Wildcard Index
  • Exclude Specific Fields from a Wildcard Index
  • Learn More

When you create a wildcard index, you can specify fields to include or exclude in the index. This lets you:

  • Create a wildcard index that only covers specific fields. For example, if you have multiple embedded documents with multiple subfields, you can create an index to cover queries on both embedded documents and their subfields.

  • Create a wildcard index that omits specific fields. For example, if you have a collection that contains a field that is never queried, you can omit that field from the index.

To include or exclude fields in a wildcard index, specify the chosen fields in the wildcardProjection option:

db.<collection>.createIndex(
{
"$**" : <sortOrder>
},
{
"wildcardProjection" : {
"<field1>" : < 0 | 1 >,
"<field2>" : < 0 | 1 >,
...
"<fieldN>" : < 0 | 1 >
}
}
)

In the wildcardProjection document, the value 0 or 1 indicates whether the field is included or excluded in the index:

  • 0 means the field is excluded.

  • 1 means the field is included.

  • To use the wildcardProjection option, your index key must be $**.

  • Wildcard indexes don't support mixing inclusion and exclusion statements in the wildcardProjection document except when explicitly including the _id field. For example:

    • The following wildcardProjection document is invalid because it specifies both an inclusion and an exclusion of a field:

      {
      "wildcardProjection" : {
      "attributes" : 0,
      "users" : 1
      }
      }
    • The following wildcardProjection document is valid because even though it specifies both inclusion and exclusion, it includes the _id field:

      {
      "wildcardProjection" : {
      "attributes" : 0,
      "_id" : 1
      }
      }

Create a products collection that contains the following documents:

db.products.insertMany( [
{
"item": "t-shirt",
"price": "29.99",
"attributes": {
"material": "cotton",
"color": "blue",
"size": {
"units": "cm",
"length": 74
}
}
},
{
"item": "milk",
"price": "3.99",
"attributes": {
"sellBy": "02-06-2023",
"type": "oat"
}
},
{
"item": "laptop",
"price": "339.99",
"attributes": {
"memory": "8GB",
"size": {
"units": "inches",
"height": 10,
"width": 15
}
}
}
] )

Each document has an attributes field that contains product details. The subfields of attributes vary depending on the product.

You can use the wildcardProjection option to:

  • Include Specific Fields in a Wildcard Index

  • Exclude Specific Fields from a Wildcard Index

If you frequently query certain document fields, you can specify those fields in a wildcardProjection to support those queries without adding unnecessary bloat to the index.

The following operation creates a wildcard index that contains all scalar values (meaning strings and numbers) of the attributes.size and attributes.color fields:

db.products.createIndex(
{
"$**" : 1
},
{
"wildcardProjection" : {
"attributes.size" : 1,
"attributes.color" : 1
}
}
)

While the key pattern "$**" covers all fields in the document, the wildcardProjection field limits the index to only the included fields.

If a field is an embedded document or array (like attributes.size), the wildcard index recurses into the field and indexes all embedded scalar field values.

The created index supports queries on any scalar value included in the wildcardProjection object. For example, the index supports these queries:

db.products.find( { "attributes.size.height" : 10 } )
db.products.find( { "attributes.color" : "blue" } )

The index does not support queries on fields not included in the wildcardProjection, such as this query:

db.products.find ( { "item": "milk" } )

If there document fields that you rarely query, you can create a wildcard index that omits those fields.

The following operation creates a wildcard index on all document fields in the products collection, but omits the attributes.memory field from the index:

db.products.createIndex(
{
"$**" : 1
},
{
"wildcardProjection" : {
"attributes.memory" : 0
}
}
)

While the key pattern "$**" covers all fields in the document, the wildcardProjection field excludes attributes.memory values from the index.

If a field is an embedded document or array (like attributes.size), the wildcard index recurses into the field and indexes all embedded scalar field values.

For example, the index supports these queries:

db.products.find( { "attributes.color" : "blue" } )
db.products.find( { "attributes.size.height" : 10 } )

The index does not support queries on attributes.memory, because that field was omitted from the index.

To learn how to use wildcard projection with a compound wildcard index to filter fields, see Filter Fields with a wildcardProjection.

To learn more about behaviors and use cases for wildcard indexes, see:

Back

Create a Wildcard Index on a Single Field

Next

Create a Wildcard Index on All Fields