I want to convert the following SQL query to MongoDB query:

SELECT count(invoiceNo), year, month, manager 
FROM battle 
WHERE year=2021 AND month='Dec' OR year=2022 AND month='Jan' AND manager = '[email protected]' 
GROUP BY year,month;

I've tried to do so, but it seems to be incorrect:

const getNoOfOrders = await BattlefieldInfo.aggregate([
 {
   $match: {
     $and: [
       {
         year: periodDate[0]['year']
       },
       { month: periodDate[0]['month'] }
     ],
     $or: [
       {
         $and: [
           {
             year: prevYear
           },
           { month: prevMonth }
         ]
       }
     ],
     $and: [{ manager: email }]
   }
 },
 {
   $group: {
     _id: '$month'
   }
 },
 {
   $project: {
     // noOfOrders: { $count: '$invoiceNo' },
     month: 1,
     year: 1,
     manager: 1
   }
 }
]);

Because I am getting an empty array. But it should be something like this:

| count(invoiceNo) |    manager    | year | month |
 ------------------ --------------- ------ ------- 
          2          [email protected]   2021    Dec
          3          [email protected]   2022    Jan

CodePudding user response：

From my point of view, I think parenthesis (bracket) is important to group the conditions together such as month and year.

SELECT count(invoiceNo), `year`, month, manager
FROM battle 
WHERE (`year` = 2021 AND month = 'Dec') 
OR (`year` = 2022 AND month = 'Jan') 
AND manager = '[email protected]'
GROUP BY month, `year`

Sample DBFiddle

Same goes for your MongoDB query. While to search with month and year, you can do without $and as below:

{
  year: 2021,
  month: "Dec"
}

Instead of:

$and: [
  {
    year: 2021
  },
  {
    month: "Dec"
  }
]

And make sure that $group stage need an accumulator operator:

noOfOrders: {
  $count: {}
}

Or

noOfOrders: {
  $sum: 1
}

Complete MongoDB query

db.collection.aggregate([
  {
    $match: {
      $or: [
        {
          year: 2021,
          month: "Dec"
        },
        {
          year: 2022,
          month: "Jan"
        }
      ],
      manager: "[email protected]"
    }
  },
  {
    $group: {
      _id: {
        month: "$month",
        year: "$year"
      },
      noOfOrders: {
        $count: {}
      },
      manager: {
        $first: "$manager"
      }
    }
  },
  {
    $project: {
      _id: 0,
      noOfOrders: 1,
      month: "$_id.month",
      year: "$_id.year",
      manager: "$manager"
    }
  }
])

Sample Mongo Playground

Note:

Would be great for both queries to add manager as one of the group keys. Since you are filtering for the specific (only one) manager's record(s), it's fine. But without filtering for specific manager, your query will result in the wrong output.