Logical Operators ($and, $or, $not)

Real-world queries rarely rest on a single condition. You need documents in stock and under a certain price, or matching one category or another, or explicitly not matching some value. MongoDB gives you three logical operators for exactly this: $and, $or, and $not (plus a fourth, $nor, for “none of these”). Understanding how each one is parsed and executed — not just how to write it — is what separates queries that scale from queries that quietly do a full collection scan.

Overview / How it works

In SQL you join conditions with the keywords AND, OR, and NOT. MongoDB queries are BSON documents, not sentences, so logical combination happens structurally, through operators that are themselves keys in that document.

The simplest and most common case is implicit AND: when you list more than one field in a query document, MongoDB requires all of them to match. db.products.find({ category: "electronics", inStock: true }) already means “category is electronics and inStock is true” — no $and needed. This works because a JS object literal can hold multiple distinct keys, and each key becomes its own condition that must all be satisfied.

You only need the explicit $and operator in two situations: when you must apply two different conditions to the same field (a plain object literal can’t hold the same key twice — the second value would just overwrite the first), or when you’re combining compound expressions that themselves contain $or, $nor, or other operators and need to control how they nest. $and takes an array of query documents, and a document matches only if it satisfies every element in that array.

$or is the counterpart: it also takes an array of query documents, but a document matches if it satisfies at least one of them. Internally, the query planner can’t always merge an $or into a single index scan the way it can with implicit AND conditions. Instead, for many query shapes MongoDB evaluates each clause of the $or as its own sub-query — using whatever index is available for that clause — and then unions the results, deduplicating by _id. This means an $or with five clauses can, in the worst case, do five separate scans. If even one clause lacks a usable index, that clause falls back to a collection scan (COLLSCAN), which can dominate the total query cost even if the other four clauses are fast.

$not is different in kind from the other two: it doesn’t combine multiple query documents, it negates a single operator expression on one field. You write it nested inside a field’s condition, like { price: { $not: { $gt: 500 } } }, meaning “price is not greater than 500.” You cannot use $not as a top-level key wrapping an entire query the way you might expect from SQL’s NOT — that’s a common and confusing mistake covered below. When you truly need to negate a whole compound query (“match none of these conditions”), that’s what $nor is for: it’s the logical negation of $or, matching documents that fail every clause in its array.

Operator Meaning Shape
$and All conditions must match { $and: [ {..}, {..} ] }
$or At least one condition must match { $or: [ {..}, {..} ] }
$not Negates a single field’s operator expression { field: { $not: { ... } } }
$nor None of the conditions may match { $nor: [ {..}, {..} ] }

Syntax

db.collection.find({
  $and: [ { <expr1> }, { <expr2> }, ... ]
});

db.collection.find({
  $or: [ { <expr1> }, { <expr2> }, ... ]
});

db.collection.find({
  <field>: { $not: { <operator-expression> } }
});
  • $and array — each element is a full query document; every element must match the same document.
  • $or array — each element is a full query document; at least one element must match.
  • $not target — must be nested under a single field and wrap an operator expression (like $gt, $eq, or a regex), not a bare value and not a whole query document.

Examples

Example 1: implicit AND vs. explicit $and. Suppose db.products holds documents like { name: "Wireless Mouse", category: "electronics", price: 250, inStock: true, rating: 4.2 }. To find electronics that are in stock, implicit AND is all you need:

db.products.find({ category: "electronics", inStock: true });

Output:

[
  { _id: ObjectId("64f1a2b3c4d5e6f7a8b9c0d1"), name: "Wireless Mouse", category: "electronics", price: 250, inStock: true, rating: 4.2 },
  { _id: ObjectId("64f1a2b3c4d5e6f7a8b9c0d2"), name: "Mechanical Keyboard", category: "electronics", price: 320, inStock: true, rating: 4.6 }
]

Now suppose you want products priced between 100 and 500. You’re applying two different conditions to the same field, price, so a plain object literal won’t work — { price: { $gt: 100 }, price: { $lt: 500 } } is invalid JS (the second price key silently overwrites the first). This is exactly when $and is required:

db.products.find({
  $and: [
    { price: { $gt: 100 } },
    { price: { $lt: 500 } }
  ]
});

Output:

[
  { _id: ObjectId("64f1a2b3c4d5e6f7a8b9c0d1"), name: "Wireless Mouse", category: "electronics", price: 250, inStock: true, rating: 4.2 }
]

Example 2: $or across different fields. Find products that are either electronics or highly rated, regardless of category:

db.products.find({
  $or: [
    { category: "electronics" },
    { rating: { $gte: 4.5 } }
  ]
});

Output:

[
  { _id: ObjectId("64f1a2b3c4d5e6f7a8b9c0d1"), name: "Wireless Mouse", category: "electronics", price: 250, inStock: true, rating: 4.2 },
  { _id: ObjectId("64f1a2b3c4d5e6f7a8b9c0d2"), name: "Mechanical Keyboard", category: "electronics", price: 320, inStock: true, rating: 4.6 },
  { _id: ObjectId("64f1a2b3c4d5e6f7a8b9c0d3"), name: "Ceramic Knife Set", category: "kitchen", price: 89, inStock: true, rating: 4.8 }
]

Notice the knife set matched purely on rating, even though its category doesn’t match the first clause — that’s the “at least one” semantics of $or at work.

Example 3: nesting $and, $or, and $not together. Find in-stock products that are electronics or books, and are not priced above 500:

db.products.find({
  $and: [
    { inStock: true },
    { $or: [ { category: "electronics" }, { category: "books" } ] },
    { price: { $not: { $gt: 500 } } }
  ]
});

Output:

[
  { _id: ObjectId("64f1a2b3c4d5e6f7a8b9c0d1"), name: "Wireless Mouse", category: "electronics", price: 250, inStock: true, rating: 4.2 },
  { _id: ObjectId("64f1a2b3c4d5e6f7a8b9c0d2"), name: "Mechanical Keyboard", category: "electronics", price: 320, inStock: true, rating: 4.6 },
  { _id: ObjectId("64f1a2b3c4d5e6f7a8b9c0d4"), name: "Clean Code", category: "books", price: 45, inStock: true, rating: 4.7 }
]

Here the outer $and requires all three array elements to hold, and its second element is itself an $or — this is the pattern you reach for whenever you need “(A or B) and C” logic, since implicit AND alone can’t express grouped alternatives.

How it works step by step

When mongosh sends a query with $and, the query planner first tries to merge the sub-conditions into a single compound index scan whenever possible — this is why implicit AND (which is really just an $and in disguise) is usually just as fast as writing $and explicitly. For example, an equality on inStock and a range on price can both be satisfied by one compound index on { inStock: 1, price: 1 } in a single pass.

For $or, the planner generally cannot merge clauses into one index scan because each clause may need a different index entirely. Instead it runs an indexed (or unindexed) scan per clause and unions the matching _ids, discarding duplicates. You can see this yourself: run db.products.find({ $or: [...] }).explain("executionStats") and look for an OR stage in the plan with multiple child stages underneath it, each showing its own IXSCAN or COLLSCAN.

For $not, MongoDB simply inverts the match result of the wrapped expression for each document it evaluates — there’s no special index strategy for it, and in many cases a negation forces a broader scan than a positive match would, since “not greater than 500” can’t be pointed at with the same precision as “equal to 500” in a B-tree index.

Common Mistakes

Mistake 1: wrapping $not around a whole query instead of a single field.

// Wrong: $not is not a top-level query operator
db.products.find({ $not: { price: { $gt: 500 } } });

This throws an error, because $not must sit directly inside a field’s condition, not wrap an entire query document. The fix is to move it under the field:

// Correct: $not negates the operator expression on price
db.products.find({ price: { $not: { $gt: 500 } } });

If what you actually meant was “none of these conditions across possibly different fields,” reach for $nor instead: db.products.find({ $nor: [ { price: { $gt: 500 } }, { category: "luxury" } ] }).

Mistake 2: duplicating a field key instead of using $and.

// Wrong: the second "price" key silently overwrites the first
db.products.find({ price: { $gt: 100 }, price: { $lt: 500 } });

In plain JavaScript this object literal only ever ends up with the last price value, so the $gt: 100 condition is lost entirely and the query becomes just “price less than 500.” Use an explicit $and array so both conditions on the same field survive:

db.products.find({
  $and: [ { price: { $gt: 100 } }, { price: { $lt: 500 } } ]
});

Mistake 3: an $or with an unindexed clause on a large collection. If category has an index but rating does not, { $or: [ { category: "electronics" }, { rating: { $gte: 4.5 } } ] } runs an efficient IXSCAN for the first clause and a full COLLSCAN for the second, on every query. On a multi-million-document collection this can be far slower than expected even though “half” the query is indexed. Check explain("executionStats") and add an index on every field used inside an $or.

Best Practices

  • Prefer implicit AND (just listing fields) over an explicit $and array — only use $and when you need duplicate conditions on the same field or explicit grouping around a nested $or/$nor.
  • Make sure every field referenced inside an $or has a usable index; an unindexed clause can force a collection scan for the whole query.
  • Keep $or clause lists short and specific — each clause is effectively evaluated as its own query internally.
  • Reach for $not only to negate a single field’s operator; use $nor when you need to negate a set of conditions across a document.
  • Where possible, rephrase a negation as a positive condition ($lte instead of $not: { $gt } ) — positive range operators are usually more index-friendly.
  • Always verify complex logical queries with .explain("executionStats") before trusting them on production-sized data.

Practice Exercises

  • Given a db.orders collection with fields status, total, and country, write a query that returns orders where status is "pending" and total is between 50 and 200 (inclusive is up to you — think about which operators to pick, and why you need $and here).
  • Write a query on db.orders that returns orders where the country is "US" or the total is greater than 1000, and explain in your own words why this can require two separate scans internally.
  • Write a query that returns products where the price is not equal to 0 (treat 0 as “out of stock / free sample” that should be excluded), using $not correctly nested under the field.

Summary

  • Listing multiple fields in a query document is implicit AND — you rarely need explicit $and.
  • $and is required when applying two conditions to the same field, or when grouping a nested $or/$nor alongside other conditions.
  • $or takes an array of alternative conditions and can require a separate index scan per clause — index every field it touches.
  • $not negates a single field’s operator expression; it cannot wrap a whole query document — use $nor for that.
  • Always confirm logical-operator queries with explain("executionStats") on realistic data volumes before shipping them.