MongoDB Query: How to add some days to a date field before comparing it with a given date

Question

The following code snippet retrieves all the users that have been modified before a given date:

val date = DateTime(...).getMillis

users.find(
  Json.obj("lastUpdate" -> Json.obj("$lt" -> Json.obj("$date" -> date))),
  None, None, page, perPage
)

How do I retrieve all the users that have been modified within a period starting from lastUpdate? In other words, I need to add some days to lastUpdate and compare it with a given date:

users.find(
  Json.obj("lastUpdate" -> /* how do I add N days to lastUpdate before comparing it with date? */
    Json.obj("$lt" -> Json.obj("$date" -> date))
  ),
  None, None, page, perPage
)

Answer 1

You can't. A simple query in MongoDB can't use values from the document it's querying. You can only use values that are constant throughout the query.

Valid Query:

{
    FirstName : "bar"
}

Invalid Query:

{
    FirstName : LastName
}

You can however acheive that by other means, for example MongoDB's aggregation framework