Mango DBLesson 19 – Logical Operators | Dataplexa

Logical Operators

Logical operators let you combine multiple filter conditions into a single query. Where comparison operators ask "is this field greater than X?", logical operators ask "does this document satisfy condition A and condition B?" or "does it satisfy A or B?". MongoDB provides four logical operators: $and, $or, $not, and $nor. Each takes an array of conditions (except $not, which wraps a single operator expression) and evaluates them together to produce a true or false result for each document. Understanding when to use each operator — and when the implicit AND shorthand is sufficient — is essential for writing clean, correct, and performant queries.

Implicit AND — The Default Behaviour

When you place multiple fields in a single filter document, MongoDB combines them with an implicit AND — every condition must be true for a document to match. This is the most common form of multi-condition query and requires no explicit operator.

Why it exists: the shorthand keeps simple queries concise. You only need explicit $and when the shorthand cannot express your intent — specifically when you need two conditions on the same field.

# Implicit AND — multiple fields in one filter dict

from pymongo import MongoClient

client = MongoClient("mongodb://localhost:27017/")
db     = client["dataplexa"]

# All three conditions must be true
# Equivalent to: membership == "premium" AND country == "UK" AND age >= 25
uk_premium_25 = db.users.find(
    {
        "membership": "premium",
        "country":    "UK",
        "age":        {"$gte": 25}
    },
    {"name": 1, "membership": 1, "country": 1, "age": 1, "_id": 0}
)
print("Premium UK users aged 25+ (implicit AND):")
for u in uk_premium_25:
    print(f"  {u['name']} — {u['country']}, age {u['age']}, {u['membership']}")

# Electronics products under $100
affordable_electronics = db.products.find(
    {"category": "Electronics", "price": {"$lt": 100}},
    {"name": 1, "price": 1, "_id": 0}
)
print("\nElectronics under $100 (implicit AND):")
for p in affordable_electronics:
    print(f"  ${p['price']:>6.2f}  {p['name']}")

Premium UK users aged 25+ (implicit AND):
Alice Johnson — UK, age 30, premium

Electronics under $100 (implicit AND):
$29.99 Wireless Mouse
$49.99 USB-C Hub
$89.99 Mechanical Keyboard

Implicit AND is always preferred over explicit $and when conditions are on different fields — it is shorter and equally performant
A filter dict can have as many fields as needed — all must match for a document to be included
The implicit AND does not work when you need two separate conditions on the same field name — a dict key can only appear once, so the second value silently overwrites the first

$and — Explicit AND

Explicit $and takes an array of condition objects and requires every one to be true. Its primary use case is when you need two or more conditions on the same field — something the implicit shorthand cannot express. It is also useful for clarity when combining complex nested conditions.

# $and — required when two conditions target the same field

from pymongo import MongoClient

client = MongoClient("mongodb://localhost:27017/")
db     = client["dataplexa"]

# WHY explicit $and is needed here:
# This SILENTLY FAILS — second "price" key overwrites the first
broken_range = {"price": {"$gte": 30}, "price": {"$lte": 100}}
# Python dict deduplicates keys — only {"price": {"$lte": 100}} survives
print("Broken range (duplicate key problem):")
print(f"  Dict kept: {broken_range}")   # only $lte remains

# CORRECT — use $and to apply two conditions to the same field
# (Normally a range on one field is written as one dict — this demo shows $and)
explicit_range = db.products.find(
    {"$and": [
        {"price": {"$gte": 30}},
        {"price": {"$lte": 100}}
    ]},
    {"name": 1, "price": 1, "_id": 0}
)
print("\nProducts $30–$100 (explicit $and):")
for p in explicit_range:
    print(f"  ${p['price']:>6.2f}  {p['name']}")

# Practical use — combine $and with $or for complex logic
# Electronics that are either highly rated OR very cheap
result = db.products.find(
    {"$and": [
        {"category": "Electronics"},
        {"$or": [
            {"rating": {"$gte": 4.6}},
            {"price":  {"$lt": 35}}
        ]}
    ]},
    {"name": 1, "category": 1, "price": 1, "rating": 1, "_id": 0}
)
print("\nElectronics with rating >= 4.6 OR price < $35:")
for p in result:
    print(f"  {p['name']:25} ${p['price']:>7.2f}  rating: {p['rating']}")

Broken range (duplicate key problem):
Dict kept: {'price': {'$lte': 100}}

Products $30–$100 (explicit $and):
$ 49.99 USB-C Hub
$ 89.99 Mechanical Keyboard

Electronics with rating >= 4.6 OR price < $35:
Wireless Mouse $ 29.99 rating: 4.5
Mechanical Keyboard $ 89.99 rating: 4.7
Monitor 27-inch $299.99 rating: 4.6

The duplicate key bug is a common Python mistake — always use explicit $and or combine both operators in a single value dict ({"price": {"$gte": 30, "$lte": 100}}) for range queries
Explicit $and is useful for readability when combining complex nested logical expressions
MongoDB's query optimiser treats explicit and implicit AND identically — there is no performance difference

$or — At Least One Condition Must Match

$or takes an array of conditions and returns documents where at least one condition is true. It is the right tool when documents may qualify through different criteria — for example, "show me products that are on sale OR have a high rating".

# $or — at least one condition must be true

from pymongo import MongoClient

client = MongoClient("mongodb://localhost:27017/")
db     = client["dataplexa"]

# Products that are either in Furniture OR cost less than $5
budget_or_furniture = db.products.find(
    {"$or": [
        {"category": "Furniture"},
        {"price": {"$lt": 5}}
    ]},
    {"name": 1, "category": 1, "price": 1, "_id": 0}
)
print("Furniture OR under $5:")
for p in budget_or_furniture:
    print(f"  {p['name']:28} ${p['price']:>7.2f}  ({p['category']})")

# Users from UK OR aged under 29
print("\nUsers from UK OR under 29:")
result = db.users.find(
    {"$or": [
        {"country": "UK"},
        {"age": {"$lt": 29}}
    ]},
    {"name": 1, "country": 1, "age": 1, "_id": 0}
)
for u in result:
    print(f"  {u['name']:15} {u['country']:8} age {u['age']}")

# $or vs $in — prefer $in when all conditions test the same field for equality
# These two are equivalent — $in is more concise and slightly more efficient:
using_or = {"$or": [{"country": "UK"}, {"country": "Spain"}, {"country": "Germany"}]}
using_in = {"country": {"$in": ["UK", "Spain", "Germany"]}}

print("\n$or vs $in — results should be identical:")
or_count = db.users.count_documents(using_or)
in_count = db.users.count_documents(using_in)
print(f"  $or count: {or_count}  |  $in count: {in_count}  |  Same: {or_count == in_count}")

Furniture OR under $5:
Standing Desk $349.99 (Furniture)
Notebook A5 $ 4.99 (Stationery)
Ballpoint Pens 10-pack $ 3.49 (Stationery)

Users from UK OR under 29:
Alice Johnson UK age 30
Bob Smith UK age 25
Clara Diaz Spain age 28

$or vs $in — results should be identical:
$or count: 3 | $in count: 3 | Same: True

When all $or conditions test the same field for equality, replace them with $in — it is more concise and MongoDB can use a single index scan
$or with conditions on different fields may require multiple index scans — one per branch — which MongoDB's query planner handles automatically
A document is returned once even if it satisfies multiple $or branches — no duplicates

$nor — All Conditions Must Be False

$nor is the logical complement of $or — it returns documents where every condition in the array is false. It also matches documents where the tested fields do not exist. Think of it as "none of the above".

# $nor — none of the conditions may be true

from pymongo import MongoClient

client = MongoClient("mongodb://localhost:27017/")
db     = client["dataplexa"]

# Orders that are neither cancelled NOR processing NOR shipped
clean_orders = db.orders.find(
    {"$nor": [
        {"status": "cancelled"},
        {"status": "processing"},
        {"status": "shipped"}
    ]},
    {"_id": 1, "status": 1, "total": 1}
)
print("Orders that are not cancelled, processing, or shipped:")
for o in clean_orders:
    print(f"  {o['_id']}  {o['status']:12}  ${o['total']:.2f}")

# Products that are NOT Electronics AND NOT Furniture
# (only Stationery remains)
non_tech = db.products.find(
    {"$nor": [
        {"category": "Electronics"},
        {"category": "Furniture"}
    ]},
    {"name": 1, "category": 1, "_id": 0}
)
print("\nProducts not in Electronics or Furniture:")
for p in non_tech:
    print(f"  {p['name']} ({p['category']})")

# $nor also matches documents missing the field entirely
# This finds users who are neither premium NOR have a membership field
result = db.users.find(
    {"$nor": [{"membership": "premium"}]},
    {"name": 1, "membership": 1, "_id": 0}
)
print("\nUsers who are not premium (including missing field):")
for u in result:
    print(f"  {u['name']} — {u.get('membership', '[no field]')}")

Orders that are not cancelled, processing, or shipped:
o001 delivered $44.96
o003 delivered $99.98
o005 delivered $329.98
o007 delivered $11.97

Products not in Electronics or Furniture:
Notebook A5 (Stationery)
Ballpoint Pens 10-pack (Stationery)

Users who are not premium (including missing field):
Bob Smith — basic
David Lee — basic

$nor like $ne also matches documents where the field is absent — keep this in mind to avoid unexpected results
$nor with a single condition is equivalent to $not applied to the whole condition
For simple "not equal to any of these values" queries, $nin is more concise than $nor

$not — Inverting a Single Condition

$not inverts the result of a single operator expression applied to one field. Unlike $nor and $or which sit at the top level of a filter, $not is applied to a field's value and must wrap an operator expression — it cannot be used with a plain equality value.

# $not — invert a single operator expression on a field

from pymongo import MongoClient

client = MongoClient("mongodb://localhost:27017/")
db     = client["dataplexa"]

# Products where price is NOT less than $50
# Equivalent to: price >= $50
not_cheap = db.products.find(
    {"price": {"$not": {"$lt": 50}}},
    {"name": 1, "price": 1, "_id": 0}
)
print("Products where price is NOT < $50:")
for p in not_cheap:
    print(f"  ${p['price']:>7.2f}  {p['name']}")

# Users where age is NOT between 25 and 35
outside_range = db.users.find(
    {"age": {"$not": {"$gte": 25, "$lte": 35}}},
    {"name": 1, "age": 1, "_id": 0}
)
print("\nUsers where age is NOT between 25 and 35:")
for u in outside_range:
    print(f"  {u['name']} — age {u['age']}")

# $not with $regex — names that do NOT start with a vowel
no_vowel_start = db.users.find(
    {"name": {"$not": {"$regex": "^[AEIOUaeiou}"}}},
    {"name": 1, "_id": 0}
)
print("\nUsers whose name does NOT start with a vowel:")
for u in no_vowel_start:
    print(f"  {u['name']}")

# Common mistake — $not cannot wrap a plain value
# WRONG:  {"membership": {"$not": "premium"}}   ← raises an error
# RIGHT:  {"membership": {"$not": {"$eq": "premium"}}}
# BETTER: {"membership": {"$ne": "premium"}}     ← simplest approach
print("\n$not vs $ne — both find non-premium users:")
not_form = db.users.count_documents({"membership": {"$not": {"$eq": "premium"}}})
ne_form  = db.users.count_documents({"membership": {"$ne": "premium"}})
print(f"  $not form: {not_form}  |  $ne form: {ne_form}  |  Same: {not_form == ne_form}")

Products where price is NOT < $50:
$ 89.99 Mechanical Keyboard
$299.99 Monitor 27-inch
$349.99 Standing Desk
$ 49.99 USB-C Hub

Users where age is NOT between 25 and 35:
Eva Müller — age 40

Users whose name does NOT start with a vowel:
Bob Smith
Clara Diaz
David Lee

$not vs $ne — both find non-premium users:
$not form: 2 | $ne form: 2 | Same: True

$not must wrap an operator expression — {"field": {"$not": "value"}} raises an error; use $ne for simple inequality instead
$not also matches documents where the field does not exist — same behaviour as $ne and $nor
Prefer $ne over {"$not": {"$eq": ...}} — it is more readable and has identical performance
$not shines when inverting complex expressions that have no simple inverse operator — like inverting a regex or a range

Combining Logical Operators

Logical operators compose freely — you can nest $or inside $and, combine $not with ranges, and build arbitrarily complex query trees. The key is keeping the structure readable.

# Combining logical operators — complex query compositions

from pymongo import MongoClient

client = MongoClient("mongodb://localhost:27017/")
db     = client["dataplexa"]

# Find orders that:
# - belong to a premium user (u001, u003, u005) AND
# - are either delivered OR have a total above $200
complex_query = db.orders.find(
    {
        "$and": [
            {"user_id": {"$in": ["u001", "u003", "u005"]}},
            {"$or": [
                {"status": "delivered"},
                {"total":  {"$gt": 200}}
            ]}
        ]
    },
    {"_id": 1, "user_id": 1, "status": 1, "total": 1}
)
print("Premium user orders — delivered OR total > $200:")
for o in complex_query:
    print(f"  {o['_id']}  user: {o['user_id']}  {o['status']:12}  ${o['total']:.2f}")

# Products that are:
# - NOT Stationery AND
# - (price > $200 OR rating >= 4.7)
premium_products = db.products.find(
    {
        "category": {"$ne": "Stationery"},
        "$or": [
            {"price":  {"$gt": 200}},
            {"rating": {"$gte": 4.7}}
        ]
    },
    {"name": 1, "category": 1, "price": 1, "rating": 1, "_id": 0}
)
print("\nNon-stationery products: price > $200 OR rating >= 4.7:")
for p in premium_products:
    print(f"  {p['name']:25} ${p['price']:>7.2f}  rating {p['rating']}")

Premium user orders — delivered OR total > $200:
o001 user: u001 delivered $44.96
o003 user: u001 delivered $99.98
o004 user: u003 processing $349.99
o005 user: u004 delivered $329.98

Non-stationery products: price > $200 OR rating >= 4.7:
Mechanical Keyboard $ 89.99 rating 4.7
Standing Desk $349.99 rating 4.8
Monitor 27-inch $299.99 rating 4.6

Mix implicit AND (comma-separated fields) with explicit $or freely — they compose cleanly in a single filter object
Deep nesting makes queries hard to read and debug — if a query has more than three levels of nesting, consider refactoring your data model
MongoDB's query planner evaluates logical operators left to right and short-circuits where possible — put the most selective condition first

Summary Table

Operator	Syntax	Matches When	Key Note
Implicit AND	`{f1: v1, f2: v2}`	All conditions true	Use for conditions on different fields
`$and`	`{"$and": [{...},{...}]}`	All conditions true	Required for two conditions on same field
`$or`	`{"$or": [{...},{...}]}`	At least one condition true	Use $in when all branches test same field
`$nor`	`{"$nor": [{...},{...}]}`	All conditions false	Also matches missing fields
`$not`	`{"field": {"$not": {op}}}`	Condition on field is false	Must wrap an operator — use $ne for simple inequality

Practice Questions

Practice 1. Why does the implicit AND shorthand fail when you need two conditions on the same field?

Practice 2. Write a filter to find all orders that are either shipped or have a total greater than $300.

Practice 3. What is the difference between $nor and $not?

Practice 4. When should you prefer $in over $or for multi-value matching?

Practice 5. Write the correct $not filter to find products where rating is NOT between 4.0 and 4.5 inclusive.

Quiz

Quiz 1. When is explicit $and required instead of the implicit AND shorthand?

When you need two or more conditions on the same field — a Python dict key can only appear once
When combining more than three conditions
When using $or in the same query
When filtering on nested fields

Quiz 2. What does $nor match in addition to documents where all conditions are false?

Documents where the tested fields do not exist at all
Documents where all conditions are null
Nothing extra
Documents where any one condition is false

Quiz 3. Why does {"membership": {"$not": "premium"}} raise an error?

$not must wrap an operator expression — it cannot be used with a plain value. Use $ne for simple inequality instead.
$not cannot be applied to string fields
$not requires an array of conditions
membership is a reserved field name

Quiz 4. If a document satisfies two branches of a $or query, how many times does it appear in the results?

Once — $or never returns duplicate documents even if multiple branches match
Twice — once per matching branch
It depends on the number of branches
It raises a DuplicateMatchError

Quiz 5. Which logical operator is most useful when you want to invert a complex regex or range expression on a single field?

$not — it inverts any operator expression applied to a single field
$nor — it negates multiple conditions at once
$ne — it negates any expression
$and with a negated condition

Next up — Array Queries: Querying into arrays with $all, $elemMatch, $size, and positional operators to find and filter embedded data.

← Previous Course Index Next →