The api is returning the date that matches and also dates that does not matches the specified date

I'm developing an api in nodejs. I have the document in following stucture:

{
    "_id" : ObjectId("5ecd26504df3372a38afffd9"),
    "balance" : 104000,
    "bankID" : "Bank-1",
    "userEmail" : "kumarshreyas073@gmail.com",
    "bankName" : "Corporation Bank",
    "accountNumber" : "03214569874563",
    "ifsCode" : "CORP0001236",
    "branch" : "Udupi",
    "address" : "Udupi",
    "city" : "Udupi",
    "state" : "Karnataka",
    "openingBalance" : 100000,
    "transactions" : [ 
        {
            "credit" : 2000,
            "debit" : 0,
            "_id" : ObjectId("5ecd26614df3372a38afffea"),
            "transactionID" : "CashTransaction-5ecd26614df3372a38afffe8",
            "date" : "30-05-2026",
            "particulars" : "By Cash-1",
            "voucherType" : "Cash"
        }, 
        {
            "credit" : 0,
            "debit" : 2000,
            "_id" : ObjectId("5ecd272d4df3372a38b00012"),
            "transactionID" : "Receipt-5ecd272d4df3372a38b00009",
            "date" : "29-07-2020",
            "particulars" : "To Suresh kumar",
            "voucherType" : "Receipt"
        }, 
        {
            "credit" : 0,
            "debit" : 2000,
            "_id" : ObjectId("5ecd272d4df3372a38b00014"),
            "transactionID" : "Receipt-5ecd272d4df3372a38b00003",
            "date" : "30-05-2024",
            "particulars" : "To Karthik",
            "voucherType" : "Receipt"
        }
    ],
    "idCounter" : 1,
    "__v" : 0
}

I need to extract only those transactions between from date = "20/07/2020" and to date = "31/07/2020".

The code I written is as follows:

exports.trail_balance = async (req, res, next) => {
  var trailBalance = {
    userEmail: req.body.userEmail,
    fromDate: req.body.fromDate,
    toDate: req.body.toDate,
  };

var bankAccount = await Bank.aggregate([
    { $match: { userEmail: req.body.userEmail } },
    {
      $addFields: {
        transactions: {
          $filter: {
            input: "$transactions",
            as: "transactions",
            cond: {
              $and: [
                {
                  $gte: ["$$transactions.date", trailBalance.fromDate],
                },

                {
                  $lte: ["$$transactions.date", trailBalance.toDate],
                },
              ],
            },
          },
        },
      },
    },
  ]);

res.status(200).json({
    result: 1,
    bankAccount: bankAccount.length > 0 ? bankAccount : [], 
  });
};

Actual result I expect is:

{
    "result": 1,
    "bankAccount": [
        {
            "_id": "5ecd26504df3372a38afffd9",
            "balance": 104000,
            "bankID": "Bank-1",
            "userEmail": "kumarshreyas073@gmail.com",
            "bankName": "Corporation Bank",
            "accountNumber": "03214569874563",
            "ifsCode": "CORP0001236",
            "branch": "Udupi",
            "address": "Udupi",
            "city": "Udupi",
            "state": "Karnataka",
            "openingBalance": 100000,
            "transactions": [
                {
                   "credit" : 0,
                   "debit" : 2000,
                   "_id" : ObjectId("5ecd272d4df3372a38b00012"),
                   "transactionID" : "Receipt-5ecd272d4df3372a38b00009",
                   "date" : "29-07-2020",
                   "particulars" : "To Suresh kumar",
                   "voucherType" : "Receipt"
               }
            ],
            "idCounter": 1,
            "__v": 0
      }

But, I'm getting all transactions. I even tried passing date in, from date = "20-07-2020" and to date = "31-07-2020". This too returns all transactions.

All the dates stored in the DB are of type String.

The problem is your date format. As you have started your date with day in your saved data in database and type of date is string, so in comparison to your query, it always start with day and it's incorrect. because in date comparison, at first years must be compared, then months and lastly, day. But you are doing it in wrong way.

In this scenario, mongodb is doing write! because in your from date, 2 is less or equal to 2 and 3 and in your to date, 3 is greater or equal to 2 and 3. So its doing well.

I changed your saved data date format to yyyy-mm-dd and your query was correct.

If changing data is not possible for you, you can also change data in a pipeline stage of your aggregate query. Use the link below:

https://docs.mongodb.com/manual/reference/operator/aggregation/dateFromString/