CODEWITHSUNDEEP
mongodb
MChan
MChan

Reputation: 7182

Mongodb Sum field within date range and other filters

Assuming I have the following document structure in my mongodb:

{
  _id: "tMSfNq9JR85XDaQe5"
  customerid: "QDGvBQhS6vYgZtnRr"
  employeeid: "QDGvBQhS6vYgZtnRr" 
  date: Sun Dec 07 2014 19:50:21 GMT+0800 (HKT) 
  projectid: "S83NEGHnrefvfASrf" 
  hours: 2
}

Is it possible to get total hours spent on a specific project and customer, grouped by employee and date, while querying data within specific date range, so that the output will be for example like:

{ "projectid":"XXX", "date":"Dec 1, 2014", "employeeid":"AAA", "totalHrs":"20" }
{ "projectid":"XXX", "date":"Dec 1, 2014", "employeeid":"BBB", "totalHrs":"11" }
{ "projectid":"XXX", "date":"Dec 2, 2014", "employeeid":"AAA", "totalHrs":"3" }
{ "projectid":"XXX", "date":"Dec 2, 2014", "employeeid":"BBB", "totalHrs":"5" }
{ "projectid":"XXX", "date":"Dec 2, 2014", "employeeid":"CCC", "totalHrs":"18" }

Thanks

Upvotes: 0

Views: 913

Answers (2)

The6thSense
The6thSense

Reputation: 8335

You need to group by projectid,date,employeeid

db.collectionname.aggregate({"$project":{"projectid":1,"date":1,"employeeid":1,"totalHrs":1}},
{"$group":{"_id":{"$projectId":"$projectid","date":"$date","employeeId":"$employeeid"}, 
"totalHrs":{"$sum":"$hours"}}})

Upvotes: 0

Neo-coder
Neo-coder

Reputation: 7840

Hi as per my understand I think you want to groups on projectid and employeeid as looking to your desired output so I think below aggregation may work in this case 

db.collectionName.aggregate(
                       {"$group":{"_id":{"projectId":"$projectid","employeeId":"$employeeid","date":"$date"},
                       "totalHrs":{"$sum":"$hours"}}},
                      {"$project":{"projectid":"$_id.projectId",
                         "date":"$_id.date","employeeid":"$_id.employeeId",
                        "totalHrs":"$totalHrs",
                          "_id":0}}
                        )

Upvotes: 1

Related Questions