Convert SQL BETWEEN to MongoDB $gte and $lte Range Query

BETWEEN to $gte + $lte

SQL's BETWEEN operator is syntactic sugar for a range check that includes both endpoints. In MongoDB, this translates to a combined $gte (greater than or equal) and $lte (less than or equal) filter on the same field.

Example SQL

SELECT * FROM orders
WHERE total BETWEEN 100 AND 500

Generated MongoDB Query

db.orders.find({
  total: { $gte: 100, $lte: 500 }
})

How Range Queries Work

MongoDB allows multiple operators on the same field within a single condition object. The query { total: { $gte: 100, $lte: 500 } } means "total is greater than or equal to 100 AND less than or equal to 500," which is exactly what BETWEEN expresses.

Date Ranges

BETWEEN is commonly used with dates in SQL:

SELECT * FROM events
WHERE event_date BETWEEN '2024-01-01' AND '2024-12-31'

In MongoDB, dates are stored as ISODate objects:

db.events.find({
  event_date: {
    $gte: ISODate("2024-01-01"),
    $lte: ISODate("2024-12-31")
  }
})

NOT BETWEEN

SQL's NOT BETWEEN can be expressed by inverting the range with $lt and $gt combined with $or:

db.orders.find({
  $or: [
    { total: { $lt: 100 } },
    { total: { $gt: 500 } }
  ]
})

Index Usage

MongoDB range queries benefit greatly from indexes. A single-field index on the queried field allows MongoDB to perform an efficient range scan instead of checking every document. For compound range queries, place the equality conditions before the range field in a compound index.