MongoDB convert string field to date and then apply gt filter

Question

I have a mongoDB collection I am working with that has the following Document structure. The date field is stored as a string not a date. I am trying to filter all the documents that are greater then a certain date but result is coming up blank.

{
    "_id": "ch_d3b8b9ab",

    "reference": "ch_d3b8b9a",
    "date": "2/2/2022 5:36 PM",
    "payment": "Visa",
    "check": "$19.00",
    "tip": "NO TIP OR CASH",
    "customer_charge": "NO SERVICE CHARGE",
    "total": "$19.00",
    "plan": "Test Plan"
}

I am trying to aggregate with following match but getting no output:

    {
    "date": {
        "$gt": [
            {
                "$dateFromString": {
                    "dateString": {
                        "$substr": [
                            "date",
                            0,
                            8
                        ]
                    },
                    "format": "%m/%d/%Y"
                }
            },
            {
                "$dateFromString": {
                    "dateString": "1/1/2021",
                    "format": "%m/%d/%Y"
                }
            }
        ]
    }
}

Also I don't think this will work for dates that have the format 2/12/2022. Any ideas would be appreciated.

Answer 1

You need $expr.

db.collection.aggregate([
  {
    $match: {
      "$expr": {
        "$gt": [
          {
            "$dateFromString": {
              "dateString": {
                "$substr": [
                  "$date",
                  0,
                  8
                ]
              },
              "format": "%m/%d/%Y"
            }
          },
          {
            "$dateFromString": {
              "dateString": "1/1/2021",
              "format": "%m/%d/%Y"
            }
          }
        ]
      }
    }
  }
])

Sample Mongo Playground