Convert SQL AND/OR Conditions to MongoDB $and/$or Operators

Logical Operators: AND and OR

SQL combines conditions with AND and OR. MongoDB provides $and and $or operators that accept arrays of conditions.

Example SQL

SELECT * FROM products
WHERE (category = 'electronics' OR category = 'books')
  AND price < 50

Generated MongoDB Query

db.products.find({
  $and: [
    { $or: [
      { category: "electronics" },
      { category: "books" }
    ] },
    { price: { $lt: 50 } }
  ]
})

Implicit AND

When all conditions are AND-ed together and apply to different fields, MongoDB uses an implicit AND by placing them in the same document:

SELECT * FROM users WHERE age >= 18 AND status = 'active'

db.users.find({
  age: { $gte: 18 },
  status: "active"
})

This is cleaner than using explicit $and. The converter uses the implicit form when possible.

Explicit $and

Explicit $and is needed when multiple conditions apply to the same field:

db.products.find({
  $and: [
    { price: { $gte: 10 } },
    { price: { $lte: 100 } }
  ]
})

Or more simply, combine operators on the same field:

db.products.find({
  price: { $gte: 10, $lte: 100 }
})

Nesting $or Inside $and

Complex SQL conditions with mixed operators translate to nested MongoDB expressions. The converter respects operator precedence: AND binds tighter than OR, just like in SQL.

Performance Tip

Place the most selective condition first in $or arrays. MongoDB evaluates $or conditions in order and can short-circuit. Indexes on fields within $or conditions are also used more effectively when each branch can leverage an index.