How to filter an array of objects in mongoose by date field only selecting the most recent date

Question

I'm trying to filter through an array of objects in a user collection on MongoDB. The structure of this particular collection looks like this:

name: "John Doe"
email: "john@doe.com"
progress: [
        {
         _id : ObjectId("610be25ae20ce4872b814b24")
         challenge: ObjectId("60f9629edd16a8943d2cab9b")
         date_unlocked: 2021-08-05T12:15:32.129+00:00
         completed: true
         date_completed: 2021-08-06T12:15:32.129+00:00
        }
        {
         _id : ObjectId("611be24ae32ce4772b814b32")
         challenge: ObjectId("60g6723efd44a6941l2cab81")
         date_unlocked: 2021-08-06T12:15:32.129+00:00
         completed: true
         date_completed: 2021-08-07T12:15:32.129+00:00
        }
]
date: 2021-08-04T13:06:34.129+00:00

How can I query the database using mongoose to return only the challenge with the most recent 'date_unlocked'?

I have tried: User.findById(req.user.id).select('progress.challenge progress.date_unlocked').sort({'progress.date_unlocked': -1}).limit(1);

but instead of returning a single challenge with the most recent 'date_unlocked', it is returning the whole user progress array.

Any help would be much appreciated, thank you in advance!

Does this answer your question? [In Mongoose, how do I sort by date? (node.js)](https://stackoverflow.com/questions/5825520/in-mongoose-how-do-i-sort-by-date-node-js) — zr0gravity7, Aug 09 '21 at 21:00
@zr0gravity7 I have updated my question with my attempt to query the database. I have tried using sort and limit with no joy! — seshkebab, Aug 09 '21 at 21:06

Takis · Accepted Answer · 2021-08-09T22:20:41.087

You can try this.

db.collection.aggregate([
  {
    "$unwind": {
      "path": "$progress"
    }
  },
  {
    "$sort": {
      "progress.date_unlocked": -1
    }
  },
  {
    "$limit": 1
  },
  {
    "$project": {
      "_id": 0,
      "latestChallenge": "$progress.challenge"
    }
  }
])

Test the code here

Alternative solution is to use $reduce in that array.

db.collection.aggregate([
  {
    "$addFields": {
      "latestChallenge": {
        "$arrayElemAt": [
          {
            "$reduce": {
              "input": "$progress",
              "initialValue": [
                "0",
                ""
              ],
              "in": {
                "$let": {
                  "vars": {
                    "info": "$$value",
                    "progress": "$$this"
                  },
                  "in": {
                    "$cond": [
                      {
                        "$gt": [
                          "$$progress.date_unlocked",
                          {
                            "$arrayElemAt": [
                              "$$info",
                              0
                            ]
                          }
                        ]
                      },
                      [
                        {
                          "$arrayElemAt": [
                            "$$info",
                            0
                          ]
                        },
                        "$$progress.challenge"
                      ],
                      "$$info"
                    ]
                  }
                }
              }
            }
          },
          1
        ]
      }
    }
  },
  {
    "$project": {
      "_id": 0,
      "latestChallenge": 1
    }
  },
])

Test the code here

Mongoose can use raw MQL so you can use it.

How to filter an array of objects in mongoose by date field only selecting the most recent date

1 Answers1