So I try to use MongoDB $match to get data between 2 dates, but it turns out that the data is not returning a.k.a empty here. What it looks like:

db.collection.aggregate([
  {
    $match: {
      date: {
        $gte: new Date("2022-10-23"),
        $lt: new Date("2022-10-25"),

      },

    }
  },
  {
    $group: {
      _id: "$title",
      title: {
        $first: "$title"
      },
      answer: {
        $push: {
          username: "$username",
          date: "$date",
          formId: "$formId",
          answer: "$answer"
        }
      }
    }
  },

])

Here is the data that I try to run on the Mongo playground: https://mongoplayground.net/p/jKx_5kZnJNz

I think there is no error with my code anymore... but why it gives an empty return.

CodePudding user response：

Migrate the comment to the answer post for the complete explanation.

Issue 1

The document contains the date field as a string type while you are trying to compare with Date which leads to incorrect output.

Ensure that you are comparing both values in the exact type.

Either that migrate the date value to Date type or

converting the date field to Date type in the query via $toDate.

{
  $match: {
    $expr: {
      $and: [
        {
          $gte: [
            {
              $toDate: "$date"
            },
            new Date("2022-10-23")
          ]
        },
        {
          $lt: [
            {
              $toDate: "$date"
            },
            new Date("2022-10-25")
          ]
        }
      ]
    }
  }
}

Issue 2

Since you are using $lt ($lt: new Date("2022-10-25")), it won't include the documents with date: new Date("2022-10-25").

For inclusive end date, you shall use $lte.

Demo @ Mongo Playground