mongodbmongodb-queryaggregation-frameworkmongodb-indexes

Can MongoDB use an index for $exists: false on an indexed field?


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.


Solution

  • 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).

    EDIT

    Just for complement the answer, you can check that index is used in this playground provided by @ray in the question comments.