GroupBy Date in MongoDB to count duplicate date records

To count duplicate date records in MongoDB, use the aggregate() method with $group to group documents by date and count occurrences.

Syntax

db.collection.aggregate([
    {
        $group: {
            _id: "$dateField",
            count: { $sum: 1 }
        }
    },
    {
        $match: { count: { $gt: 1 } }
    }
]);

Sample Data

db.demo160.insertMany([
    { "DueDate": new ISODate("2019-01-11") },
    { "DueDate": new ISODate("2019-01-11") },
    { "DueDate": new ISODate("2020-02-01") },
    { "DueDate": new ISODate("2020-02-01") },
    { "DueDate": new ISODate("2020-04-10") }
]);

{
    "acknowledged": true,
    "insertedIds": [
        ObjectId("5e357532fdf09dd6d0853a05"),
        ObjectId("5e357538fdf09dd6d0853a07"),
        ObjectId("5e357525fdf09dd6d0853a04"),
        ObjectId("5e357534fdf09dd6d0853a06"),
        ObjectId("5e357542fdf09dd6d0853a08")
    ]
}

View Sample Data

db.demo160.find();

{ "_id": ObjectId("5e357532fdf09dd6d0853a05"), "DueDate": ISODate("2019-01-11T00:00:00Z") }
{ "_id": ObjectId("5e357538fdf09dd6d0853a07"), "DueDate": ISODate("2019-01-11T00:00:00Z") }
{ "_id": ObjectId("5e357525fdf09dd6d0853a04"), "DueDate": ISODate("2020-02-01T00:00:00Z") }
{ "_id": ObjectId("5e357534fdf09dd6d0853a06"), "DueDate": ISODate("2020-02-01T00:00:00Z") }
{ "_id": ObjectId("5e357542fdf09dd6d0853a08"), "DueDate": ISODate("2020-04-10T00:00:00Z") }

Group by Date and Count Duplicates

db.demo160.aggregate([
    {
        $group: {
            _id: {
                $add: [
                    { $dayOfYear: "$DueDate" },
                    { $multiply: [400, { $year: "$DueDate" }] }
                ]
            },
            Frequency: { $sum: 1 },
            date: { $min: "$DueDate" }
        }
    },
    { $sort: { _id: 1 } },
    { $limit: 100 },
    { $project: { date: "$date", Frequency: 1, _id: 0 } }
]);

{ "Frequency": 2, "date": ISODate("2019-01-11T00:00:00Z") }
{ "Frequency": 2, "date": ISODate("2020-02-01T00:00:00Z") }
{ "Frequency": 1, "date": ISODate("2020-04-10T00:00:00Z") }

How It Works

• $dayOfYear extracts the day number (1-366) from the date
• $multiply: [400, {$year: "$DueDate"}] creates a unique year identifier
• $add combines them to create a unique date identifier
• $sum: 1 counts occurrences for each date
• $min: "$DueDate" preserves the original date format

Conclusion

Use MongoDB's aggregation pipeline with $group to count duplicate dates. The combination of $dayOfYear and $year operators ensures accurate date grouping regardless of time components.