Mongodb count distinct with multiple group fields

[karthick kb]

Hi ,

Need help on following mongodb logic.

I have  transaction table which is populated by holidays taken by the employees.

I would need help on following sql scenario 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 was created mongo query like this and I have tried something like this and have not completed the count distinct logic here.

db.transactions.aggregate( 

 { $group : { _id : { "Month": { $month : "$date"}, "Year": { $year : "$date"},"employee" : "$employee",Count_of_Transactions : { $sum :1}

  }}

  );

I am confused in using count distinct logic in mongodb. Any suggestion would be helpful

--
Regards
Karthick

[Andrew Ryder]

Hi Karthick!

I think you intend something like this:

{
  { $group: { _id: { employee: "$employee", year: "$year", month: "$month" }, Array_of_Transactions : { $addToSet: "$holiday_type" } } },
  { $project: { _id: "$_id", Count_of_Transactions:{ $size: "$Array_of_Transactions" } } }
}

The $group operation removes repeats of the combination "employee, year, month" and creates an array of distinct "holiday_type" observed in the process. The second stage $project replaces the array for the size of the array. If you only perform the first stage $group you get the array of distinct values instead, which might be useful to you.

By the way, your schema seems very SQL-like. I think a lot of the documents in this collection could easily be merged to provide a better/faster experience with this kind of operation.

I hope this helps.

Kind regards,
Andrew