Aggregation Match Mongodb with dateFromString

Question

Dear attempts to filter by date in mongo but I have not succeeded. I pass my query and structure of JSON.

db.getCollection('articles').aggregate([
{ "$match": {
 "$expr": {
  "$and": [
    {
      "$gte": [
        { "$dateFromString": { "creationDate": "10-08-2018", "format": "%m-%d-%Y" }}
      ]
    },
    {
      "$lte": [
        { "$dateFromString": { "creationDate": "10-08-2018", "format": "%m-%d-%Y" }}
      ]
    }
  ]
}
}}
])

My JSON is

{
"_id" : ObjectId("5bbb6b1de75b933850a608fc"),
"title" : "05",
"body" : "asgfasgasfa",
"creationDate" : ISODate("2018-10-08T14:35:07.000Z"),
"operationType" : "C",
"__v" : 0
}

MongoDB : v3.6.3

Answer 1

If you a looking for a match on "10-08-2018" exactly from a date field you can use $dateToString in combination with $eq:

db.getCollection('articles').aggregate([
  {
    "$match": {
      "$expr": {
        $eq: [
          "10-08-2018",
          {
            "$dateToString": {
              "date": "$creationDate",
              "format": "%m-%d-%Y"
            }
          }
        ]
      }
    }
  }
])

See it working here

If you are looking for a set of records matching a date range:

db.getCollection('articles').aggregate([
  {
    "$match": {
      "$expr": {
        "$and": [
          {
            "$gte": [
              "$creationDate",
              {
                "$dateFromString": {
                  "dateString": "10-08-2018",
                  "format": "%m-%d-%Y"
                }
              }
            ]
          },
          {
            "$lte": [
              "$creationDate",
              {
                "$dateFromString": {
                  "dateString": "11-08-2018",
                  "format": "%m-%d-%Y"
                }
              }
            ]
          }
        ]
      }
    }
  }
])

See it working here

One note with the 2nd example is that it would do the dates as ISO date so it would not cover the end of day 11-08-2018 but 2018-11-08T00:00:00.000Z as pointed out by matthPen comment.

Answer 2

Even if @Akrion answer is returning sample, it not properly filter dates. In a global way, it's better to deal with dates as often as you can, instead of transforming to string. A simple example : using your format ("%m-%d-%Y"), Date("10-08-2018") > Date("12-01-2017"), but in term of string comparison, "10-08-2018" < "12-01-2017" . The only working format in this case is %Y-%m-%d .

Anyway, mongoDB provides some operators in aggregation framework, to properly work with dates. Here's a query to match by day :

db.collection.aggregate([
  {
    $match: {
      $expr: {
        $and: [
          {
            $eq: [
              {
                $year: new Date("2018-10-08")
              },
              {
                $year: "$creationDate"
              }
            ]
          },
          {
            $eq: [
              {
                $month: new Date("2018-10-08")
              },
              {
                $month: "$creationDate"
              }
            ]
          },
          {
            $eq: [
              {
                $dayOfMonth: new Date("2018-10-08")
              },
              {
                $dayOfMonth: "$creationDate"
              }
            ]
          }
        ]
      }
    }
  }
])

You can try it here

It can be a little more tricky to adapt this query for dates range, but the following query will do the job. You can test it here.

db.collection.aggregate([
  {
    $match: {
      $expr: {
        $and: [
          {
// Start date definition, included ($gte)
            $or: [
              {
                $and: [
                  {
                    $eq: [
                      {
                        $year: new Date("2018-10-08")
                      },
                      {
                        $year: "$creationDate"
                      }
                    ]
                  },
                  {
                    $eq: [
                      {
                        $month: new Date("2018-10-08")
                      },
                      {
                        $month: "$creationDate"
                      }
                    ]
                  },
                  {
                    $gte: [
                      {
                        $dayOfMonth: "$creationDate"
                      },
                      {
                        $dayOfMonth: new Date("2018-10-08")
                      }
                    ]
                  }
                ]
              },
              {
                $and: [
                  {
                    $eq: [
                      {
                        $year: "$creationDate"
                      },
                      {
                        $year: new Date("2018-10-08")
                      }
                    ]
                  },
                  {
                    $gte: [
                      {
                        $month: "$creationDate"
                      },
                      {
                        $month: new Date("2018-10-08")
                      }
                    ]
                  },

                ]
              },
              {
                $and: [
                  {
                    $gte: [
                      {
                        $year: "$creationDate"
                      },
                      {
                        $year: new Date("2018-10-08")
                      }
                    ]
                  },

                ]
              }
            ],

          },
     //end date definition, excluded ($lt)
          {
            $or: [
              {
                $and: [
                  {
                    $eq: [
                      {
                        $year: new Date("2018-11-08")
                      },
                      {
                        $year: "$creationDate"
                      }
                    ]
                  },
                  {
                    $eq: [
                      {
                        $month: new Date("2018-11-08")
                      },
                      {
                        $month: "$creationDate"
                      }
                    ]
                  },
                  {
                    $lt: [
                      {
                        $dayOfMonth: "$creationDate"
                      },
                      {
                        $dayOfMonth: new Date("2018-11-08")
                      }
                    ]
                  }
                ]
              },
              {
                $and: [
                  {
                    $eq: [
                      {
                        $year: "$creationDate"
                      },
                      {
                        $year: new Date("2018-10-08")
                      }
                    ]
                  },
                  {
                    $lt: [
                      {
                        $month: "$creationDate"
                      },
                      {
                        $month: new Date("2018-11-08")
                      }
                    ]
                  },

                ]
              },
              {
                $and: [
                  {
                    $lt: [
                      {
                        $year: "$creationDate"
                      },
                      {
                        $year: new Date("2018-11-08")
                      }
                    ]
                  },

                ]
              }
            ],

          }
        ]
      }
    }
  }
])