Multiple conditions inside match MongoDB

Question

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 = 'name@test.com' 
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          name@test.com   2021    Dec
          3          name@test.com   2022    Jan

Hi, are you sure your SQL query is correct? 1. You need bracket (parenthesis) for month and year 2021-Dec and 2022-Jan separately. 2. Your MongoDB query filter for manager, while SQL query doesn't contain this filter. 3. Your SQL query need to group by Month & Year. For MongoDB query, concern on the syntax, the $group need accumulator operator. — Yong Shun
@YongShun yes correct, thank you for pointing it out. I updated the question with SQL query and Mongodb query. I fixed the problem with error but now im getting an empty array — Yevgeniy Bagackiy

Yong Shun Yong Shun · Accepted Answer · 2022-01-20T08:24:30

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 = 'abc@email.com'
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: "abc@email.com"
    }
  },
  {
    $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.

Multiple conditions inside match MongoDB

1 Answers