How to use MongoDB aggregation to get only those records whose index is divisible by 5?

Question

Here is what my data looks like in a collection named usages:

{ _id: AUTOGENERATED_MONGO_OBJECT_ID, timeStamp: 1675122960, usage: null, limit: 2.7 },
{ _id: AUTOGENERATED_MONGO_OBJECT_ID, timeStamp: 1675123020, usage: 2.74, limit: 2.7 },
{ _id: AUTOGENERATED_MONGO_OBJECT_ID, timeStamp: 1675123080, usage: 2.62, limit: 2.7 },
{ _id: AUTOGENERATED_MONGO_OBJECT_ID, timeStamp: 1675123140, usage: 4.32, limit: 2.7 },
{ _id: AUTOGENERATED_MONGO_OBJECT_ID, timeStamp: 1675123200, usage: null, limit: 2.7 },
{ _id: AUTOGENERATED_MONGO_OBJECT_ID, timeStamp: 1675123260, usage: 5.38, limit: 2.7 },
{ _id: AUTOGENERATED_MONGO_OBJECT_ID, timeStamp: 1675123320, usage: 5.08, limit: 2.7 },
{ _id: AUTOGENERATED_MONGO_OBJECT_ID, timeStamp: 1675123380, usage: 1.02, limit: 2.7 },
{ _id: AUTOGENERATED_MONGO_OBJECT_ID, timeStamp: 1675123440, usage: 2.35, limit: 2.7 },

Please Note: This is just sample of how my data looks, actually, I have 40000 records.

I am trying to get last 60 records of data using this mongoose query:

  const count = await Usage.count()
  Usage.find({}).sort({ timeStamp: 1 }).skip(count - 60);

So far, everything is working fine.

But now, there is a requirement to get less data in the required range. Let's say output needs to have only 12 records from the last 60 records without losing much data. We will be plotting a line chart on the frontend using this data.

So, I can filter the data such that I will pick data whose index is divisible by 5.

So, here is my code:

    const count = await Usage.count();
    const data = await Usage
        .find({})
        .sort({ timeStamp: 1 })
        .skip(count - start)
        .exec();
    const requiredData = data.filter((item, index) => index % 5 === 0);
    console.log(requiredData);

The above code should work as expected, but it is very slow because I am filtering data after I get it from the database.

Ideally, I should filter the data using some query so the database should give me filtered results only.

Can someone help me to achieve that?

Here is a very similar question: filter Items of Array by Index in Aggregation Pipeline

But in that question, OP wants to filter based on a particular field named arr. But in my case, I want to filter based on the whole collection. And I don't have much knowledge of how can I write a query using aggregate. I tried some video tutorials to learn aggregate, but it looks a bit complex, and need some more time to learn it.

Answer 1

Try this one:

db.collection.aggregate([
   { $sort: { t: -1 } },
   { $limit: 60 },
   {
      $setWindowFields: {
         sortBy: { t: 1 },
         output: {
            pos: { $documentNumber: {} }
         }
      }
   },
   { $set: { remainder: { $mod: ["$pos", 5] } } },
   { $match: { remainder: 1 } },
   { $sort: { t: 1 } },
   { $unset: ["pos", "remainder"] }
])

Mongo Playground

With $setWindowFields you could do it even more advanced, for example skip the document if difference to previous document is less than xyz.