Query Operators and Complex Queries

Now that you're comfortable with basic CRUD operations, it's time to level up your querying skills. In this lesson, you'll learn how to use MongoDB's powerful query operators to create sophisticated queries that can filter, compare, and combine conditions in ways that go far beyond simple equality matches.

Learning Goals:

• Understand and use comparison operators ($gt, $lt, $in, etc.)
• Master logical operators ($and, $or, $not, $nor)
• Learn array operators for querying nested data
• Combine multiple operators for complex query conditions
• Use regex patterns for flexible text searching

Comparison Operators

Comparison operators let you query documents based on field values meeting specific conditions, not just exact matches.

Basic Comparison Operators

comparison_operators.js

// Find products with price greater than 100
db.products.find({ price: { $gt: 100 } })

// Find users aged 18 to 30 (inclusive)
db.users.find({ age: { $gte: 18, $lte: 30 } })

// Find orders not in "pending" status
db.orders.find({ status: { $ne: "pending" } })

// Find products in specific categories
db.products.find({ category: { $in: ["electronics", "books"] } })

// Find products NOT in these categories
db.products.find({ category: { $nin: ["clothing", "furniture"] } })

tip

Use $in instead of multiple $or conditions when checking field equality against multiple values. It's more concise and often performs better.

Working with Dates

date_queries.js

// Find orders created in the last 7 days
const oneWeekAgo = new Date();
oneWeekAgo.setDate(oneWeekAgo.getDate() - 7);

db.orders.find({ 
  createdAt: { $gte: oneWeekAgo } 
})

// Find users who signed up in 2024
db.users.find({
  signupDate: {
    $gte: new Date("2024-01-01"),
    $lt: new Date("2025-01-01")
  }
})

Logical Operators

Logical operators allow you to combine multiple query conditions.

AND Conditions

and_operator.js

// Implicit AND (both conditions must match)
db.products.find({
  category: "electronics",
  price: { $lt: 500 }
})

// Explicit AND (useful for same field conditions)
db.products.find({
  $and: [
    { price: { $gte: 100 } },
    { price: { $lte: 1000 } },
    { inStock: true }
  ]
})

OR and NOR Conditions

or_nor_operators.js

// Find products that are either on sale OR low stock
db.products.find({
  $or: [
    { onSale: true },
    { stock: { $lt: 10 } }
  ]
})

// Find products that are NOT on sale AND NOT low stock
db.products.find({
  $nor: [
    { onSale: true },
    { stock: { $lt: 10 } }
  ]
})

// Complex combination
db.users.find({
  $and: [
    { $or: [{ role: "admin" }, { role: "moderator" }] },
    { isActive: true },
    { $nor: [{ isBanned: true }, { loginAttempts: { $gte: 5 } }] }
  ]
})

warning

Be careful with complex logical conditions. Test each part individually before combining them to ensure your query logic is correct.

Array Operators

Array operators are essential for working with fields that contain arrays.

Querying Array Elements

array_operators.js

// Find documents where tags contains "mongodb"
db.articles.find({ tags: "mongodb" })

// Find documents where tags contains ALL specified elements
db.articles.find({ tags: { $all: ["mongodb", "database"] } })

// Find documents where array size is exactly 3
db.products.find({ images: { $size: 3 } })

// Find documents where array contains at least one element matching condition
db.users.find({ scores: { $elemMatch: { $gte: 90 } } })

// More complex elemMatch
db.courses.find({
  assignments: {
    $elemMatch: {
      dueDate: { $gte: new Date("2024-06-01") },
      status: "pending"
    }
  }
})

Working with Nested Arrays

nested_arrays.js

// Find users who have ordered a specific product
db.users.find({
  "orders.items.productId": "prod_123"
})

// Find users who spent more than $100 in any order
db.users.find({
  "orders.total": { $gt: 100 }
})

// Using array index position
db.users.find({
  "orders.0.status": "shipped" // First order is shipped
})

Regular Expression Queries

MongoDB supports regex patterns for flexible text searching.

regex_queries.js

// Case-sensitive: find users with "john" in their name
db.users.find({ name: /john/ })

// Case-insensitive: find users with "john" (any case)
db.users.find({ name: /john/i })

// Using $regex operator for dynamic patterns
const searchTerm = "data";
db.articles.find({ 
  title: { $regex: searchTerm, $options: "i" } 
})

// Starts with pattern
db.products.find({ 
  sku: { $regex: "^PROD-", $options: "i" } 
})

// Complex regex: email pattern matching
db.users.find({
  email: { $regex: /^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}$/ }
})

Putting It All Together: Complex Query Examples

Let's combine multiple operators in realistic scenarios.

JavaScript

complex_queries.js

// E-commerce query: Find available electronics under $500 
// with good ratings, excluding specific brands
db.products.find({
  $and: [
    { category: "electronics" },
    { price: { $lt: 500 } },
    { inStock: true },
    { $or: [
      { rating: { $gte: 4 } },
      { "reviews.count": { $gt: 50 } }
    ]},
    { brand: { $nin: ["BrandX", "BrandY"] } }
  ]
})

// User analytics: Find active premium users 
// who logged in recently but haven't completed profile
db.users.find({
  $and: [
    { accountType: "premium" },
    { isActive: true },
    { lastLogin: { $gte: new Date("2024-05-01") } },
    { $or: [
      { profileComplete: false },
      { profileComplete: { $exists: false } }
    ]},
    { $nor: [
      { isSuspended: true },
      { emailVerified: false }
    ]}
  ]
})

TypeScript

complex_queries.ts

// TypeScript interface for better type safety
interface Product {
  category: string;
  price: number;
  inStock: boolean;
  rating?: number;
  reviews?: { count: number };
  brand: string;
}

// The same e-commerce query with TypeScript
const electronicsQuery = {
  $and: [
    { category: "electronics" as const },
    { price: { $lt: 500 } },
    { inStock: true },
    { $or: [
      { rating: { $gte: 4 } },
      { "reviews.count": { $gt: 50 } }
    ]},
    { brand: { $nin: ["BrandX", "BrandY"] } }
  ]
};

// Execute the query
const results = await db.collection<Product>('products')
  .find(electronicsQuery)
  .toArray();

Common Pitfalls

• Overusing $or: Complex $or conditions can be slow. Consider restructuring your data or using $in when possible.
• Missing operator syntax: Remember that operators like $gt must be used inside objects: {field: {$gt: value}}, not {field: $gt: value}.
• Regex performance: Unanchored regex searches (/pattern/) can be slow on large collections. Use anchored patterns (/^pattern/) when possible.
• Array query confusion: {tags: "mongodb"} finds documents where "mongodb" is in the array, not where the array equals ["mongodb"].
• Type mismatches: MongoDB is type-sensitive. The number 10 is different from the string "10" in queries.

Summary

You've now mastered MongoDB's powerful query operators that take you beyond basic CRUD operations. You can:

• Use comparison operators for range queries and value matching
• Combine conditions with logical operators for complex business logic
• Query array fields and nested documents effectively
• Perform flexible text searches with regular expressions
• Build sophisticated queries by combining multiple operator types

These skills form the foundation for writing efficient, maintainable queries that can handle real-world application requirements.

Quiz

Show quiz

1. Which query finds products with prices between $50 and $100 (inclusive)?

   • A) db.products.find({price: {$gt: 50, $lt: 100}})
   • B) db.products.find({price: {$gte: 50, $lte: 100}})
   • C) db.products.find({price: [50, 100]})
   • D) db.products.find({price: {$between: [50, 100]}})

2. What does $all operator do when querying arrays?

   • A) Matches if array contains any of the specified elements
   • B) Matches if array contains all of the specified elements
   • C) Matches if array size equals the specified number
   • D) Matches if array contains elements matching conditions

3. Which query finds users whose email ends with "@company.com"?

   • A) db.users.find({email: /@company.com/})
   • B) db.users.find({email: /^@company.com/})
   • C) db.users.find({email: /@company.com$/})
   • D) db.users.find({email: {$endWith: "@company.com"}})

4. What's the difference between implicit AND and explicit $and?

   • A) Implicit AND is faster, explicit $and is more readable
   • B) Implicit AND only works for different fields, explicit $and works for same field
   • C) There's no functional difference
   • D) Explicit $and is required for complex queries

5. Which operator would you use to exclude documents where a field equals specific values?

   • A) $ne
   • B) $not
   • C) $nin
   • D) $nor

---

Answers:

1. B - $gte and $lte provide inclusive range matching
2. B - $all matches documents where the array contains all specified elements
3. C - The $ anchor matches the end of the string
4. B - Implicit AND applies to different fields, while explicit $and can combine conditions on the same field
5. C - $nin (not in) excludes documents where field value is in the specified array