Comparison Query Operators ($eq, $ne, $gt, $gte, $lt, $lte, $in, $nin)

Level 3 — CRUD Operations (Create, Read, Update, Delete) The BSON comparison operators used in query filters to evaluate document values, serving as the direct equivalents of SQL's relational symbols (=, <>, >, >=, <, <=, IN, NOT IN).

1. Prerequisites

• Query Filter (Filter Document) — The parent filter parameter structure.
• find() / findOne() — Finding documents using comparison filters.

2. Term Category

Query Operator (Value Comparison Operators): Comparison Operators ($eq,$gt, $gte,$lt, $lte,$ne, $in,$nin) match document fields based on value comparison criteria.

3. Explanation

Environment Context

• Universal Standard (Supported natively by all document NoSQL platforms. Handled by the index scanner engine to perform ranged indexes queries).

(1) Design Motivation — "Why did we design this?"

When filtering data, we rarely look for exact matches:

• An e-commerce store needs to find items priced under $50.
• A booking app needs to find flights departing on or after a specific date.
• An admin dashboard needs to find orders that are not cancelled.

In PostgreSQL, you use standard mathematical symbols: SELECT * FROM products WHERE price >= 100.00 AND category IN ('shoes', 'books');

Because MongoDB filters are JSON objects, we cannot use loose characters like >= or IN as operators directly in keys.

We designed the Comparison Operators (prefixed with $) to allow you to express mathematical comparisons as clean JSON sub-documents, enabling the database compiler to map them directly to BSON index ranges.

(2) SQL to BSON Operator Mapping

(3) Reality Metaphor

Imagine a quality control inspector checking metal parts on a conveyor belt:

• $gt / $lt: A physical Go/No-Go Gauge.
  • The inspector has a metal caliper set to exactly 10mm.
  • If a part is larger than the gap ($gt), it passes through the checkpoint.
  • If it's smaller, it falls off the belt.
• $in: A checklist board of Authorized Part Numbers.
  • If the incoming box label matches any serial number written on the board, it is approved.
  • Otherwise, it is rejected.

(4) Code Examples

Range Filtering (gt and lte)

// Find all products priced between 10.00 and 50.00 (inclusive)
db.products.find({
  price: { 
    $gte: NumberDecimal("10.00"), 
    $lte: NumberDecimal("50.00") 
  }
});

List Filtering (in and ne)

// Find active users who are NOT administrators and are in the sales or support teams
db.users.find({
  role: { $ne: "admin" },
  team: { $in: ["sales", "support"] }
});

4. Common Mistakes & Pitfalls

Mistake 1: Forgetting to nest the comparison operator inside a subdocument wrapper

The mistake: Writing the query { age: $gt: 25 } or { age: $gt 25 } in your database query filters.

Why it's wrong: This is invalid JSON syntax.

The parser expects a key-value structure.

The operator must be the key of a nested subdocument object.

Fix: Always wrap comparison operators inside curly braces under the field key: { field: { $operator: value } }.

// CORRECT
db.users.find({ age: { $gt: 25 } });

Mistake 2: Using String Numbers in Numeric Comparison Operators ($gt, $lt)

The mistake: Querying { age: { $gt: "18" } } when age is stored as BSON integer number 18.

Why it's wrong: MongoDB compares string "18" against number 18 using BSON Type Comparison Order. Strings sort higher than numbers, returning unexpected query results.

Incorrect:

db.users.find({ age: { $gt: "18" } }); // ❌ String comparison against number field!

Fix:

db.users.find({ age: { $gt: 18 } }); // Numeric comparison

Mistake 3: Confusing $in Array Values with Single Element Predicates

The mistake: Writing { status: { $in: "active" } } passing a scalar string.

Why it's wrong: $in strictly expects an array of values { status: { $in: ["active", "pending"] } }.

Incorrect:

db.users.find({ status: { $in: "active" } }); // ❌ Expected array!

Fix:

db.users.find({ status: { $in: ["active", "pending"] } });

5. Practice Exercises

Exercise 1: Querying Range Thresholds with $gt and $lt

Scenario: Query collection products for items with price between $20.00 and $100.00.

Requirements:

1. Combine $gt: 20.00 and $lt: 100.00.

db.products.find({
  price: { $gt: 20.00, $lt: 100.00 }
});

Exercise 2: Matching Discrete Values with $in

Scenario: Query collection orders for documents where status is either "pending", "processing", or "shipped".

Requirements:

1. Use $in: ["pending", "processing", "shipped"].

db.orders.find({
  status: { $in: ["pending", "processing", "shipped"] }
});

Exercise 3: Inequality Filtering with $ne

Scenario: Query user documents where role is NOT equal to "admin".

Requirements:

1. Use $ne: "admin".

db.users.find({
  role: { $ne: "admin" }
});

6. Related Terms

• Query Filter (Filter Document) — The parent filter layout.
• Logical Query Operators ($and, $or, $not, $nor) — - Combining filters.

7. Key Takeaways

• BSON comparison query operators evaluate field values mathematically.
• Direct equivalents of SQL relational symbols (=, >, <=, IN).
• Written nested under the field key: { field: { $operator: value } }.
• $in matches if a field value matches any element in a list array.
• $nin matches if a field value matches none of the elements in a list.
• Combine range operators targeting one field inside a single nested object.
• Utilizing comparison operators allows index scans to resolve range queries.