MongoDB $not vs $or + $exists: false: Which is more efficient for queries including missing fields?

Working with Data
1

I’m designing a MongoDB query where I want to select documents based on a numeric field, but also include documents where the field is missing. For example, consider a collection with an age field:

// Option 1: Using $not
db.collection.find({ age: { $not: { $lt: 18 } } })

// Option 2: Using $or + $exists: false
db.collection.find({
  $or: [
    { age: { $gte: 18 } },
    { age: { $exists: false } }
  ]
})

Both queries return the same results — documents with age >= 18 or where age is missing.

My questions are:

  1. Which of these query patterns is generally more efficient in terms of index utilization?

  2. Does MongoDB treat $not differently internally when the field is missing?

  3. Are there situations where one approach scales better, especially with compound indexes?

I would like to understand best practices for writing queries that include missing fields while taking full advantage of available indexes.

1 Like
2

Hey buddy,
long story short;

  1. Which is more efficient? $or with { $gte } + { $exists: false }, because each branch can use its own index.
  2. Does $not behave differently with missing fields? Yes. $not matches >= 18, and missing, null, or non-numeric values — it’s broader than $or.
  3. Which scales better with compound indexes? $or scales better. You can tune each branch with its own (possibly partial) index. $not usually can’t take advantage of compound indexes efficiently.
2 Likes