Combining Conditions: $and, $or and $not

Two fields in one filter already mean "and"; $or matches when any of its conditions does, $and holds conditions that can't share one document, and $not and $nor rule documents out.

Apprentice · MongoDB · Updated

By

After this lesson you can

  • Combine conditions with two fields in one filter, $or and $and, and read a combined filter aloud
  • Rule documents out with $not and $nor, including the documents that don't have the field

Key takeaways

  • Two fields in one filter already mean and. Both must match.
  • $or takes a list of conditions in square brackets and matches a document when any one of them does.
  • A field written twice in one filter keeps only its last condition, so put both conditions in the field's own braces or use $and.
  • $not flips one field's condition, and $nor matches the documents where none of its conditions do.
  • $not and $nor also match documents that don't have the field at all.

Real questions come in pairs and in choices: "vegetarian and quick", "quick, or so good it's worth the wait", "anything but the desserts". A store's filters work the same way when you tick a brand and a price range together. You'll combine conditions in almost every filter you write for an app, and reading a combined filter correctly is what keeps it from quietly answering a different question.

The examples use Sunday Kitchen's 40 recipes from Adding Many Documents with insertMany.

Two fields already mean "and"

You've been combining conditions since Finding Documents with find and findOne: every field in a filter must match. Vegetarian recipes that take 15 minutes or less:

db.recipes.find({ vegetarian: true, minutes: { $lte: 15 } }, { name: 1, minutes: 1 })
[
  { _id: 8, name: 'Miso Soup', minutes: 15 },
  { _id: 9, name: 'Greek Salad', minutes: 15 },
  { _id: 16, name: 'French Toast', minutes: 15 },
  { _id: 20, name: 'Guacamole', minutes: 10 },
  { _id: 32, name: 'Overnight Oats', minutes: 5 },
  { _id: 35, name: 'Hummus', minutes: 10 }
]

$or: either one will do

Sunday Kitchen's "worth making tonight" list wants recipes that are very quick, 10 minutes or less, or very well liked, rated 4.8 or higher. Either is enough. $or takes a list of conditions in square brackets, each one a filter of its own in braces:

db.recipes.find({ $or: [ { minutes: { $lte: 10 } }, { rating: { $gte: 4.8 } } ] }, { name: 1, minutes: 1, rating: 1 })
[
  { _id: 3, name: 'Lemon Bars', minutes: 50, rating: 4.8 },
  { _id: 10, name: 'Chicken Tikka Masala', minutes: 60, rating: 4.9 },
  { _id: 14, name: 'Beef Pho', minutes: 180, rating: 4.8 },
  { _id: 20, name: 'Guacamole', minutes: 10, rating: 4.3 },
  { _id: 23, name: 'Chocolate Chip Cookies', minutes: 30, rating: 4.9 },
  { _id: 32, name: 'Overnight Oats', minutes: 5, rating: 3.6 },
  { _id: 35, name: 'Hummus', minutes: 10, rating: null }
]

Read it aloud as "recipes where minutes is at most 10, or the rating is at least 4.8". Beef Pho takes three hours but is rated 4.8, and Hummus isn't rated yet but takes 10 minutes: each passes one condition, and one is enough. In everyday speech "or" sometimes means "one but not both"; $or always includes the documents that pass both.

Two overlapping circles. The left circle, 10 minutes or less, holds Guacamole, Overnight Oats and Hummus. The right circle, rated 4.8 or higher, holds Lemon Bars, Chicken Tikka Masala, Beef Pho and Chocolate Chip Cookies. Both circles, the overlap included, are shaded, because $or finds everything inside either one.

The same field twice: use $and

Here's a mistake that gives no error. Someone wants the recipes that take 20 to 40 minutes and writes the field twice:

db.recipes.countDocuments({ minutes: { $gte: 20 }, minutes: { $lte: 40 } })
27

A document can't have the same field twice, so when a filter repeats one, the last one wins: this filter is only { minutes: { $lte: 40 } }, which also finds the 5-minute Overnight Oats. The right count is 21. Either put both operators in the field's own braces, as Comparison Operators: $gt, $lt, $in and More did, or list the two conditions separately inside $and. This one is split over several lines with two-space indents; mongosh waits for the last closing bracket before it runs a command, so you can type or paste it as it is:

db.recipes.countDocuments({
  $and: [
    { minutes: { $gte: 20 } },
    { minutes: { $lte: 40 } }
  ]
})
21

$and matches when every condition in its list does. Most of the time two fields in one filter say it for you; you need $and when the conditions can't sit side by side in one document, such as two conditions on one field, or two $or lists.

$not and $nor

$not flips one field's condition. You might expect "rating not 4.5 or higher" to give the same answer as "rating less than 4.5". It doesn't:

db.recipes.countDocuments({ rating: { $lt: 4.5 } })
19
db.recipes.countDocuments({ rating: { $not: { $gte: 4.5 } } })
26

The 7 extra recipes are the ones nobody has rated yet. $lt: 4.5 only matches a rating that is a number below 4.5, while $not matches every document where the condition isn't true, including those with no rating to compare. Missing Fields, null and $exists looks at those recipes closely.

$nor (read "nor", as in "neither this nor that") takes a list like $or, and matches the documents where none of the conditions do. Recipes that are neither American nor over 30 minutes:

db.recipes.countDocuments({ $nor: [ { cuisine: "American" }, { minutes: { $gt: 30 } } ] })
16

Read a filter aloud before you run it

Nested filters are where mistakes hide, so read one aloud, from the outside in, before you press Enter. A spice-night menu wants Mexican or Indian recipes that are also vegetarian or quick:

db.recipes.find({
  $and: [
    { $or: [ { cuisine: "Mexican" }, { cuisine: "Indian" } ] },
    { $or: [ { vegetarian: true }, { minutes: { $lte: 20 } } ] }
  ]
}, { name: 1 })
[
  { _id: 4, name: 'Chickpea Curry' },
  { _id: 7, name: 'Black Bean Tacos' },
  { _id: 20, name: 'Guacamole' },
  { _id: 21, name: 'Palak Paneer' },
  { _id: 27, name: 'Huevos Rancheros' },
  { _id: 39, name: 'Churros' }
]

"Recipes where the cuisine is Mexican or Indian, and they're vegetarian or take 20 minutes or less." Two $or lists can't sit side by side in one document, for the same reason as the repeated field above, so $and holds them. Beef Tacos are Mexican but neither vegetarian nor quick, so they're left out. If what you read aloud isn't the question you meant, fix the filter before you trust its answer.

Check yourself

2 questions to try on the lesson page.

So what

A combined filter says exactly one thing, and it isn't always what it looks like at a glance. Read it aloud, watch for a field written twice, and remember that "not" also finds the documents that are missing the field.

Key terms

Related