MongoDB query to find oldest date of three keys in each document

To find the oldest date among three date fields in each MongoDB document, use the $min operator within an aggregation pipeline. This operator compares multiple date values and returns the smallest (oldest) one.

Syntax

db.collection.aggregate([
    {
        $project: {
            OldestDate: {
                $min: ["$dateField1", "$dateField2", "$dateField3"]
            }
        }
    }
])

Sample Data

db.demo353.insertMany([
    {
        "Date1": new ISODate("2019-01-10"),
        "Date2": new ISODate("2016-01-21"),
        "Date3": new ISODate("2020-04-11")
    },
    {
        "Date1": new ISODate("2011-11-20"),
        "Date2": new ISODate("2013-12-10"),
        "Date3": new ISODate("2014-01-05")
    }
]);

{
    "acknowledged": true,
    "insertedIds": [
        ObjectId("..."),
        ObjectId("...")
    ]
}

Display Sample Data

db.demo353.find();

{ "_id": ObjectId("5e568261f8647eb59e5620be"), "Date1": ISODate("2019-01-10T00:00:00Z"), "Date2": ISODate("2016-01-21T00:00:00Z"), "Date3": ISODate("2020-04-11T00:00:00Z") }
{ "_id": ObjectId("5e56827ff8647eb59e5620bf"), "Date1": ISODate("2011-11-20T00:00:00Z"), "Date2": ISODate("2013-12-10T00:00:00Z"), "Date3": ISODate("2014-01-05T00:00:00Z") }

Query to Find Oldest Date

db.demo353.aggregate([
    {
        $project: {
            OldestDate: {
                $min: [
                    { $ifNull: ["$Date1", new Date()] },
                    { $ifNull: ["$Date2", new Date()] },
                    { $ifNull: ["$Date3", new Date()] }
                ]
            }
        }
    }
]);

{ "_id": ObjectId("5e568261f8647eb59e5620be"), "OldestDate": ISODate("2016-01-21T00:00:00Z") }
{ "_id": ObjectId("5e56827ff8647eb59e5620bf"), "OldestDate": ISODate("2011-11-20T00:00:00Z") }

Key Points

• The $min operator compares date values and returns the earliest (oldest) date.
• $ifNull handles cases where date fields might be missing or null.
• The $project stage creates a new field containing only the oldest date.

Conclusion

Use $min with $project in an aggregation pipeline to efficiently find the oldest date among multiple date fields. The $ifNull operator ensures robust handling of missing date values.