1

I am new to MongoDb. I need help to fetch the last n month record, there might be multiple entry per month but the query needs to return only the last entry per month.

For e.g lets say if n is 3 and userId is userId1 (that means return last 3 month record for userId1).

Sample inputs in the collection:

[
 {
   "_id": objectId("aaaaaa"),
   "userId": "userId1",
   "processedAt": "2021-06-01T12:16:49.349Z"
 },
 {
   "_id": objectId("bbbbb"),
   "userId": "userId1",
   "processedAt": "2021-10-11T12:16:49.349Z"
 },
 {
   "_id": objectId("ccccc"),
   "userId": "userId1",
   "processedAt": "2021-10-25T12:16:49.349Z"
 },
 {
   "_id": objectId("eeeee"),
   "userId": "userId1",
   "processedAt": "2021-09-12T12:16:49.349Z"
 },
 {
   "_id": objectId("fffff"),
   "userId": "userId1",
   "processedAt": "2021-09-28T12:16:49.349Z"
 },
 {
   "_id": objectId("ggggg"),
   "userId": "userId1",
   "processedAt": "2021-09-23T12:16:49.349Z"
 },
 {
   "_id": objectId("hhhhh"),
   "userId": "userId1",
   "processedAt": "2021-07-23T12:16:49.349Z"
 },
 {
   "_id": objectId("iiiii"),
   "userId": "userId2",
   "processedAt": "2021-09-29T12:16:49.349Z"
 },
 {
   "_id": objectId("jjjjj"),
   "userId": "userId1",
   "processedAt": "2022-01-29T12:16:49.349Z"
 },
 {
   "_id": objectId("kkkkk"),
   "userId": "userId1",
   "processedAt": "2022-02-29T12:16:49.349Z"
 }, 
]

Expected Result: Should return by userId, limit n months(fetch only the last saved entry of the month) and the ascending order of the month of processedAt:

[{
    "_id": objectId("ccccc"),
    "userId": "userId1",
    "processedAt": "2021-10-25T12:16:49.349Z"
},
{
    "_id": objectId("jjjjj"),
    "userId": "userId1",
    "processedAt": "2022-01-29T12:16:49.349Z"
  },
  {
    "_id": objectId("kkkkk"),
    "userId": "userId1",
    "processedAt": "2022-02-29T12:16:49.349Z"
  }
]

I have tried below query however which is returning all the records. I want query needs to consider only the last entry per month. I have been using mongojs driver v4.1.2

db.collection(collection_name)
                .find({ userId: userId }, { projection: { _id: 0 } })
                .sort({ processedAt: -1 })
                .limit(n)
                .toArray()
ray
  • 11,310
  • 7
  • 18
  • 42
sham
  • 422
  • 4
  • 18

1 Answers1

2

Starting from MongoDB 5.0,

You can use $setWindowFields to aggregate a "rank" for the "partition" / "group" (i.e. the month in your example) and only choose the document with top rank.

The ranking can be defined as processedAt: -1 as you want to keep only the latest record in the month with highest rank.

{
    "$setWindowFields": {
      "partitionBy": {
        "$dateToString": {
          "date": "$processedAt",
          "format": "%Y-%m"
        }
      },
      "sortBy": {
        "processedAt": -1
      },
      "output": {
        "rank": {
          $rank: {}
        }
      }
    }
  }

Here is the Mongo playground for your reference.

For MongoDB 3.6+,

As the sample dataset is using ISODate format, it is possible to sort and group the field by leftmost 7 characters (i.e. yyyy-MM). Keeping only the first document inside the month group should do the tricks.

{
    $sort: {
      processedAt: -1
    }
  },
  {
    "$addFields": {
      "month": {
        "$substrCP": [
          "$processedAt",
          0,
          7
        ]
      }
    }
  },
  {
    $group: {
      _id: "$month",
      last: {
        $first: "$$ROOT"
      }
    }
  }

Here is the Mongo playground.

ray
  • 11,310
  • 7
  • 18
  • 42
  • I have mongo 3.x. and using mongojs driver v4.1.2 – sham Oct 22 '21 at 08:00
  • What is the exact version of MongoDB you are using? – ray Oct 22 '21 at 08:02
  • I am using MongoDb v3.6 – sham Oct 22 '21 at 08:04
  • @sham I have updated another solution for older version. It should be compatible with the MongoDB 3.6 that you are using. – ray Oct 22 '21 at 08:12
  • Its adding intermediate field `month` How can I skip that and keep original document? Also Is there a way to sort the result by ascending order of month? – sham Oct 22 '21 at 08:37
  • 1
    @sham just add 1 more `$project` to remove the helper field. Updated the playground link to reflect that – ray Oct 22 '21 at 09:12
  • I tried adding $match and $limit plus updated input data(different userId and date with new year) that seems to be not returning expected result https://mongoplayground.net/p/U6H_5WPFs7h – sham Oct 22 '21 at 09:39
  • Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/238427/discussion-between-ray-and-sham). – ray Oct 22 '21 at 09:44
  • ok..lets jump to chat – sham Oct 22 '21 at 09:53