I have the following documents in my collection:

[
    {"date_time": "2022-11-05 09:09:55", "dat1": "TRUI", "cod": "XC"}
    {"date_time": "2022-11-21 09:09:55", "dat1": "TRQW", "cod": "KL"}
    {"date_time": "2022-12-06 09:09:55", "dat1": "CBTR", "cod": "NM"}
    {"date_time": "2022-12-18 09:09:55", "dat1": "METR", "cod": "XC"}
]

So, I'd like to query my collection to get all documents with the conditions "cod": "XC" and "date_time" between 2022-11-01 to 2022-12-31. The result would be:

[
    {"date_time": "2022-12-18 09:09:55", "dat1": "METR", "cod": "XC"}
    {"date_time": "2022-11-05 09:09:55", "dat1": "TRUI", "cod": "XC"}
]

How can I achieve the result?

CodePudding user response：

As the date_time field is a String type, you need to convert it from String to DateTime type via $dateFromString operator. The operator is an aggregation operator, thus you need the $expr operator.

db.collection.find({
  $expr: {
    $and: [
      {
        $eq: [
          "$cod",
          "XC"
        ]
      },
      {
        $and: [
          {
            $gte: [
              {
                $dateFromString: {
                  dateString: "$date_time",
                  format: "%Y-%m-%d %H:%M:%S"
                }
              },
              ISODate("2022-11-01T00:00:00Z")
            ]
          },
          {
            $lte: [
              {
                $dateFromString: {
                  dateString: "$date_time",
                  format: "%Y-%m-%d %H:%M:%S"
                }
              },
              ISODate("2022-12-31T00:00:00Z")
            ]
          }
        ]
      }
    ]
  }
})

Demo ($dateFromString) @ Mongo Playground

As it is a field for storing date, would suggest storing the value as DateTime type. This will simplify and optimize your query without need to perform the data conversion.

db.collection.find({
  cod: "XC",
  date_time: {
    $gte: ISODate("2022-11-01T00:00:00Z"),
    $lte: ISODate("2022-12-31T00:00:00Z")
  }
})

Demo @ Mongo Playground