CODEWITHSUNDEEP
mongodbaggregation-framework
Marzack
Marzack

Reputation: 57

MongoDB Aggeregation Pipeline: Get last N-th record

Here is my current code in MongoDB aggregation pipeline:

db.mydatabase.aggregate([
  {
    "$project" : 
      {
        "mpmonth":{"$last":"$month"},
      }
   }
])

At the moment, this code only allows getting the latest month (e.g. July), I want to get the records of 6 months before (e.g. January).

Is there a way to get last N-th of month records and how can I do that?

Updated:

Thanks to @AndrewShmig's suggestion, here is my approach to the problem:

db.mydatabase.aggregate([
  {
    "$sort" : {
      "seq" : -1
    }
  },
  {
    "$skip" : 6
  }
])

In this, I add in another column of data named:seq, I $sort the records in descending order, then $skip the first 6 records, and now the first record will shows the "$Month"="Jan". Finally, in my JavaScript, I select the first record:

$group": { 
        
        "mpmonth":{"$first":"$mpmonth"}
}

Upvotes: 0

Views: 74

Answers (1)

Valijon
Valijon

Reputation: 13103

Alternative solution using the $arrayElemAt operator.

db.mydatabase.aggregate([
  {
    "$sort": {
      "seq": -1
    }
  },
  {
    $group: {
      _id: null,
      "month": {
        "$push": "$month"
      }
    }
  },
  {
    $project: {
      mpmonth: {
        $arrayElemAt: ["$month", 6] //N-th element, being -1 the last element
      }
    }
  }
])

MongoPlayground

Upvotes: 1

Related Questions