MongoDB aggregation result difference

Question

I'm trying to compose a query to find all documents in a collection with the 'date' < current date, flag = true, sort by date (descending) and limit the result to (e.g.) 10 documents, then select all the remaining documents (satisfying the date and flag conditions) in the collection for an update to be performed. SQL equivalent would be:

SELECT * FROM TABLE
WHERE DATE < SYSDATE
AND FLAG = TRUE
MINUS
SELECT * FROM TABLE
WHERE DATE < SYSDATE
AND FLAG = TRUE
ORDER BY DATE DESC
LIMIT 10

So far I have:

db.scratch.aggregate(
  [
    { $match: { flag: true, date: { $lt: new Date() } } },
    { $sort: { date: -1 } },
    { $limit: 10 }
  ]
)

Is it possible to make the subtraction in the DB, or the only option I have is to process the results of two queries on the client side?

Answer 1

This is actually just a simple query as MINUS merely does an "exclusion" and all you are really saying boils down to "exclude the first 10 results by these criteria".

db.scratch.find({ "date": { "$lt": new Date() }, "flag": true })
  .sort({ "date": -1 }).skip(10);

So "limit the first 10 and then exclude those results from the overall" is basically reduced to SKIP, on the same criteria.