When you create a wildcard index in MongoDB, you have the option of specifying a single field, all fields, or just some.

You also have the option of excluding certain fields. In other words, you can specify all fields except for one or more specific fields.

You can use the wildcardProjection parameter to include or exclude specific field paths from the wildcard index. This article presents an example of excluding specific fields in the wildcard index.

Example Document

Suppose we have a collection called pets with the following documents:

{
	"_id" : 1,
	"name" : "Wag",
	"details" : {
		"type" : "Dog",
		"weight" : 20,
		"awards" : {
			"Florida Dog Awards" : "Top Dog",
			"New York Marathon" : "Fastest Dog",
			"Sumo 2020" : "Biggest Dog"
		}
	}
}
{
	"_id" : 2,
	"name" : "Fetch",
	"details" : {
		"born" : ISODate("2020-06-22T14:00:00Z"),
		"color" : "Black"
	}
}
{
	"_id" : 3,
	"name" : "Scratch",
	"details" : {
		"eats" : [
			"Mouse Porridge",
			"Bird Soup",
			"Caviar"
		],
		"type" : "Cat",
		"born" : ISODate("2020-12-19T14:00:00Z")
	}
}

We could create a wildcard index on the whole collection, while excluding certain fields.

Create the Index

Here’s an example:

db.pets.createIndex(
  { "$**" : 1 },
  {
    "wildcardProjection" : {
      "details.awards" : 0,
      "details.eats" : 0
    }
  }
)

Output:

{
	"createdCollectionAutomatically" : false,
	"numIndexesBefore" : 1,
	"numIndexesAfter" : 2,
	"ok" : 1
}

The { "$**" : 1 } part is what creates the wildcard index, and the wildcardProjection part is the part that specifies which fields to exclude. In this case we’ve excluded the details.awards field and the details.eats field. Giving them a value of 0 explicitly excludes them from the index.

View the Index

We can see the indexes on the collection by calling the getIndexes() method:

db.pets.getIndexes()

Result:

[
	{
		"v" : 2,
		"key" : {
			"_id" : 1
		},
		"name" : "_id_"
	},
	{
		"v" : 2,
		"key" : {
			"$**" : 1
		},
		"name" : "$**_1",
		"wildcardProjection" : {
			"details.awards" : 0,
			"details.eats" : 0
		}
	}
]

We can see that there are two indexes.

• The first index is on the _id field. This was created when the collection was created (MongoDB creates a unique index on the _id field during the creation of a collection).
• The second index is our wildcard index. We can see that it’s been automatically named $**_1, and it includes the fields that we specified along with a value of 0, which means that they’re explicitly excluded from the index.

Test the Index

We can also run some queries to see whether our index will be used, and whether the excluded fields really will be excluded

The following query should use the index:

db.pets.find( { "details.type" : "Dog" } )

It should use the index because we didn’t exclude the details.type field from the index.

To test this, we can append the explain() method to view the query plan:

db.pets.find( { "details.type" : "Dog" } ).explain()

Result:

{
	"queryPlanner" : {
		"plannerVersion" : 1,
		"namespace" : "PetHotel.pets",
		"indexFilterSet" : false,
		"parsedQuery" : {
			"details.type" : {
				"$eq" : "Dog"
			}
		},
		"queryHash" : "F1C5286F",
		"planCacheKey" : "5326DE93",
		"winningPlan" : {
			"stage" : "FETCH",
			"inputStage" : {
				"stage" : "IXSCAN",
				"keyPattern" : {
					"$_path" : 1,
					"details.type" : 1
				},
				"indexName" : "$**_1",
				"isMultiKey" : false,
				"multiKeyPaths" : {
					"$_path" : [ ],
					"details.type" : [ ]
				},
				"isUnique" : false,
				"isSparse" : false,
				"isPartial" : false,
				"indexVersion" : 2,
				"direction" : "forward",
				"indexBounds" : {
					"$_path" : [
						"[\"details.type\", \"details.type\"]"
					],
					"details.type" : [
						"[\"Dog\", \"Dog\"]"
					]
				}
			}
		},
		"rejectedPlans" : [ ]
	},
	"ok" : 1
}

We can see that it used an index scan (IXSCAN) on our index.

In contrast to this, here’s what happens when we run a query on one of the fields that we excluded from the index:

db.pets.find( { "details.awards.Florida Dog Awards" : "Top Dog" } ).explain()

Result:

{
	"queryPlanner" : {
		"plannerVersion" : 1,
		"namespace" : "PetHotel.pets",
		"indexFilterSet" : false,
		"parsedQuery" : {
			"details.awards.Florida Dog Awards" : {
				"$eq" : "Top Dog"
			}
		},
		"queryHash" : "16FBC17B",
		"planCacheKey" : "16FBC17B",
		"winningPlan" : {
			"stage" : "COLLSCAN",
			"filter" : {
				"details.awards.Florida Dog Awards" : {
					"$eq" : "Top Dog"
				}
			},
			"direction" : "forward"
		},
		"rejectedPlans" : [ ]
	},
	"ok" : 1
}

In this case it did a collection scan (COLLSCAN), so as expected, it didn’t use the index.