Given a aggregation pipeline like this, in which I use a $match
with { $exists: false }
on a indexed field:
db.collection.aggregate([
{
$match: {
myField: { $exists: false }
}
}
])
I’m wondering if this filter can take advantage of the index, or if it will always result in a collection scan.
Reading MongoDB documentation here, I guess that:
If your index is a sparse index ({ sparse: true })
, the answer is pretty simple: the index won’t be used, because documents in which the field is absent are not indexed.
If your index is a regular (non-sparse) index, the index can be used. This type of index includes all documents, even those that don’t have the field, because they are indexed with a null value. When you search with { $exists: false }
, MongoDB can use the index to find entries with a null value, but after that a FETCH stage is needed to check that the field is truly absent and not simply set to null. So, in this case, the index is still useful, because it avoids doing a full collection scan (COLLSCAN).
Just for complement the answer, you can check that index is used in this playground provided by @ray in the question comments.