Mongo $lt doesn't work as expected when comparing dates

August 17, 2022

Problem

I have the following aggregation query that compares today’s date with a fixed date and should set result to "pass" if today’s date is less than the future date.

However the "$lt" case isn’t fulfil and thus result is always set to "fail"

Document

[
  {
    "future_date": {
      "$date": {
        "$numberLong": "62135596800000"
      }
    }
  }
]

Query

   db.collection.aggregate([
  {
    "$addFields": {
      "current_date": new Date(),
      "future_date": new Date(62135596800000),
      "result": {
        $switch: {
          branches: [
            {
              case: {
                "$lt": [
                  new Date(),
                  "$future_date"
                ]
              },
              then: "pass"
            }
          ],
          default: "fail"
        }
      }
    }
  }
])

Result

 [
  {
    "_id": ObjectId("5a934e000102030405000000"),
    "current_date": ISODate("2022-08-17T13:21:36.552Z"),
    "future_date": ISODate("3939-01-01T00:00:00Z"),
    "result": "fail"
  }
]

According to the Mongo docs, I have structured the "$lt" statement correctly, so I’m not quite sure what I’m missing.

Playground Example

https://mongoplayground.net/p/VECSNjQWESc

>Solution :

You can’t add a field and use it in the same stage.

Corrected pipeline with two $addFields stages:

db.collection.aggregate([
  {
    "$addFields": {
      "future_date": new Date(62135596800000),  // override the future_date field in the document with this value
    }
  },
  {
    "$addFields": {
      "current_date": new Date(),
      "status": {
        $switch: {
          branches: [
            {
              case: {
                "$lt": [
                  new Date(),
                  "$future_date"
                ]
              },
              then: "pass"
            }
          ],
          default: "fail"
        }
      }
    }
  }
])

The document you have shown in playground is not the same as the one in the question:

[
  {
    "future_date": {
      "date": {            // this is date not $date
        "$numberLong": "62135596800000"   // this is number, not date
      }
    }
  }
]

For this, the aggregation would be:

db.collection.aggregate([
  {
    "$addFields": {
      "current_date": new Date(),
      "future_date": new Date(62135596800000),
      "status": {
        $switch: {
          branches: [
            {
              case: {
                "$lt": [
                  new Date(),
                  {
                    $toDate: "$future_date.date"  // convert to date before comparison
                  }
                ]
              },
              then: "pass"
            }
          ],
          default: "fail"
        }
      }
    }
  }
])