Trying to aggregate on multiple fields Mongoose

Question

I have created a time entry system in which users can enter in the amount of time (percentage) spent on a task between a given time period. Each record looks like the following. I changed the user _id to explicit names to make it easier to visualize

"project_name": "first_project",
"linked_project": "5bd057f5d4b8173d88b7fe47",
"percentage": 25,
"user": {
    "$oid": "Steve"
},

"project_name": "first_project",
"linked_project": "5bd057f5d4b8173d88b7fe47",
"percentage": 50,
"user": {
    "$oid": "Steve"
},

"project_name": "second_project",
"linked_project": "5bd057f5d4b8173d88b7fe48",
"percentage": 25,
"user": {
    "$oid": "Steve"
},

"project_name": "second_project",
"linked_project": "5bd057f5d4b8173d88b7fe48",
"percentage": 75,
"user": {
    "$oid": "Mary"
},

I'm trying to first group by Person and then by project. Basically I want a total of how much each user has spent on a particular task. Not sure if what I am trying to achieve is even possible. I have included what I am trying to achieve below:

Example output:

[
    {
        user: Steve,
        projects: [
            first_project: 75,
            second_project: 25
        ]
    },
    {
        user: Mary,
        projects: [
            second_project: 75
        ]
    }

]

I've tried a variety of ways to achieve this and I haven't come close. Hopefully someone has some insight on how to achieve this.

s7vr · Accepted Answer · 2018-10-25 12:33:47Z

1

You can use multiple groups, one for summing percentages for each user and project_name combination and second group to push all the documents for user.

db.colname.aggregate([
  {"$group":{
    "_id":{
      "user":"$user",
      "project_name":"$project_name"
    },
    "time":{"$sum":"$percentage"}
  }},
  {"$group":{
    "_id":"$_id.user",
    "projects":{"$push":{"project_name":"$_id.project_name","time":"$time"}}
  }}
])

To get the output as single document you can use in the last group stage

"projects":{"$mergeObjects":{"$arrayToObject":[[["$_id.project_name","$time"]]]}}

answered Oct 25, 2018 at 12:33

s7vr

76.2k12 gold badges118 silver badges135 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Sam Munroe Over a year ago

How would I add a sort so the array of projects is in order by name?

s7vr Over a year ago

Add {$sort:{"_id.project_name":1}} before group stage.

Collectives™ on Stack Overflow

Trying to aggregate on multiple fields Mongoose

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related