Mongodb calculated with multiple group fields

Question

I have a transaction table that is populated with holidays occupied by employees. I need help with the following sql script in mongodb.

select employee,month,year,count(distinct (holiday_type) from transactions group by employee,month,year 

I need to use aggregation in mongodb and a mongo request like this was created and this gives me the wrong solution.

 db.transactions.aggregate([ { "$group": { "_id": { "Month": { "$month" : "$date" }, "Year": { "$year" : "$date" }, "employee" : "$employee", "holiday_type" : "$holiday_type" }, "Count_of_Transactions" : { "$sum" : 1 } }} ]); 

I am confused about using counting logic in mongodb. Any suggestion would be helpful

Answer 1

Part of the way, but you need to get the "great" values ​​for "holiday_type" first, then $group again:

 db.transactions.aggregate([ { "$group": { "_id": { "employee" : "$employee", "Month": { "$month" : "$date" }, "Year": { "$year" : "$date" }, "holiday_type" : "$holiday_type" }, }}, { "$group": { "_id": { "employee" : "$_id.employee", "Month": "$_id.Month", "Year": "$_id.Year" }, "count": { "$sum": 1 } }} ], { "allowDiskUse": true } ); 

This is a common process as "excellent" in SQL, it is a kind of grouping operation in itself. So this is a double operation of $group to get the correct result.