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
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