MongoDB query range date with certain other key-CodePudding

I've documents on 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 condition "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