Mongodb aggregate, match multiple $eq expressions using $and

Question

I have a mongodb query. i am trying to use the match operator to match two different dates to 'createdAt' fields to return documents that have the appropriate month and year.

I have two date variables named "prevMonth" and "lastMonth".

const date = new Date();
const lastMonth = new Date(date.setMonth(date.getMonth() -1)); //24th December 2022
const prevMonth = new Date(date.setMonth(lastMonth.getMonth() -1)); //24th January 2022

Here are sample documents:

{
        _id:636d40818b6adc8d4fecb56b
        amount:95
        address:"UK"
        createdAt:2022-12-17T18:18:41.049+00:00
}

{
        _id:636d40818b6adc8d4fecb56b
        amount:95
        address:"UK"
        createdAt:2022-12-17T18:18:41.049+00:00
}

{
        _id:636d40818b6adc8d4fecb56b
        amount:95
        address:"UK"
        createdAt:2022-11-17T18:18:41.049+00:00
}

{
        _id:636d40818b6adc8d4fecb56b
        amount:95
        address:"UK"
        createdAt:2023-01-17T18:18:41.049+00:00
}

I am using $dateFromParts, to match the entire month and the year (because i want to find the 'amount' accumulated for the entire month regardless of what the day might be. Each month would return the months total sales).

In theory, the first document should pass the month (12) and year (2022) of 'createdAt' and be compared against the constant month and year of prevMonth (12/2022) and should return a document and the same process done for the next documents.

{
  $match: {
    $expr: {
      $and: [
        {
          $eq: [
            {
              $dateFromParts: {
                'month': { $month: '$createdAt' },
                'year': { $year: '$createdAt' }
              }
            },
            {
              $dateFromParts: {
                'month': { $month: prevMonth },
                'year': { $year: prevMonth }
              }
            }
          ],
        },
        {
          $eq: [
            {
              $dateFromParts: {
                'month': { $month: '$createdAt' },
                'year': { $year: '$createdAt' }
              }
            },
            {
              $dateFromParts: {
                'month': { $month: lastMonth },
                'year': { $year: lastMonth }
              }
            }
          ]
        }
      ]
    }
  }
}

In theory, my code should look up all documents with a 'createdAt' field that match the month and year of the date variables applied. 'prevMonth'(2022-12-24T03:18:48.029Z) and 'lastMonth' (2023-01-24T03:18:48.029Z).

Going by the sample JSON code above, there should be 3 documents matched and returned, two that match the month and year of prevMonth 12/2022 and one matching lastMonth 01/2023.

But when i put this code to the test, an empty array is returned. If i remove the $and operator and only match either one of the two $eq operator expressions used above i get a populated array.

My question is quite literally the same as Match multiple conditions in an aggregate under expressions , only difference is they've queried $lt paired with $eq. Where as mine are two $eq queries to match against $prevMonth and $lastMonth variables.

Answer 1

You can perform the comparison on $year and $month only. Return the result if both year and month matches for either prevMonth or lastMonth

db.collection.aggregate([
  {
    $addFields: {
      prevMonth: ISODate("2022-12-24T03:18:48.029Z"),
      lastMonth: ISODate("2023-01-24T03:18:48.029Z")
    }
  },
  {
    $match: {
      $expr: {
        $or: [
          // prevMonth
          {
            $and: [
              {
                $eq: [
                  {
                    $year: "$createdAt"
                  },
                  {
                    $year: "$prevMonth"
                  }
                ]
              },
              {
                $eq: [
                  {
                    $month: "$createdAt"
                  },
                  {
                    $month: "$prevMonth"
                  }
                ]
              }
            ]
          },
          // lastMonth
          {
            $and: [
              {
                $eq: [
                  {
                    $year: "$createdAt"
                  },
                  {
                    $year: "$lastMonth"
                  }
                ]
              },
              {
                $eq: [
                  {
                    $month: "$createdAt"
                  },
                  {
                    $month: "$lastMonth"
                  }
                ]
              }
            ]
          },
          
        ]
      }
    }
  },
  {
    $unset: [
      "lastMonth",
      "prevMonth"
    ]
  }
])

Mongo Playground