A single MongoDB query to orderby date and group by user

To order by date and group by user in a single MongoDB query, use the aggregate() method with $group and $sort stages. This approach groups documents by user and finds the maximum date for each user, then sorts the results.

Syntax

db.collection.aggregate([
    {
        $group: {
            _id: "$userId",
            dueDate: { $max: "$dueDate" }
        }
    },
    {
        $sort: { dueDate: -1 }
    }
]);

Sample Data

db.demo243.insertMany([
    {"userId": 1, "dueDate": new ISODate("2019-01-10")},
    {"userId": 2, "dueDate": new ISODate("2019-11-10")},
    {"userId": 2, "dueDate": new ISODate("2020-01-31")},
    {"userId": 1, "dueDate": new ISODate("2010-01-10")}
]);

Display all documents from the collection ?

db.demo243.find();

{ "_id" : ObjectId("5e4575f81627c0c63e7dba5f"), "userId" : 1, "dueDate" : ISODate("2019-01-10T00:00:00Z") }
{ "_id" : ObjectId("5e4576011627c0c63e7dba60"), "userId" : 2, "dueDate" : ISODate("2019-11-10T00:00:00Z") }
{ "_id" : ObjectId("5e4576151627c0c63e7dba61"), "userId" : 2, "dueDate" : ISODate("2020-01-31T00:00:00Z") }
{ "_id" : ObjectId("5e45761b1627c0c63e7dba62"), "userId" : 1, "dueDate" : ISODate("2010-01-10T00:00:00Z") }

Example: Group by User and Get Latest Date

Group documents by userId and find the maximum dueDate for each user ?

db.demo243.aggregate([
    {
        $group: {
            _id: "$userId",
            dueDate: { $max: "$dueDate" }
        }
    }
]);

{ "_id" : 2, "dueDate" : ISODate("2020-01-31T00:00:00Z") }
{ "_id" : 1, "dueDate" : ISODate("2019-01-10T00:00:00Z") }

Key Points

• $group groups documents by the specified field (userId in this case).
• $max operator finds the maximum date value within each group.
• Add $sort stage after grouping to order the final results by date.

Conclusion

Use MongoDB's aggregate() with $group and $max to group documents by user and extract the latest date. This single query efficiently combines grouping and date ordering operations.