mongodb pipeline aggregation group results into array

Question

I have a projects collection:

{
  _id: 1,
  title: "Project 1"
},
{
  _id: 2,
  title: "Project 2"
}

and a (time) entries collection:

{
  _id: 90123,
  project_id: 1,
  task_id: 1,
  hours: 3
},
{
  _id: 90124,
  project_id: 1,
  task_id: 1,
  hours: 5
},
{
  _id: 90125,
  project_id: 2,
  task_id: 2,
  hours: 1
},
{
  _id: 90126,
  project_id: 1,
  task_id: 2,
  hours: 2
}

I'd like to use a pipeline aggregation to:

Get entries for project 1
Sum all entries for project 1 as "totalSpent"
Group by task_id with summed hours per task

Something like this as the end result:

{
  totalSpent: 10,
  spentByTask: {
    { task_id: 1, spent: 8 },
    { task_id: 2, spent: 2 }
  }
}

Dĵ ΝιΓΞΗΛψΚ · Accepted Answer · 2020-03-10 03:08:15Z

3

give this pipeline a try:

db.entries.aggregate(
    [
        {
            $match: { project_id: 1 }
        },
        {
            $group: {
                _id: '$task_id',
                spent: { $sum: '$hours' }
            }
        },
        {
            $group: {
                _id: null,
                totalSpent: { $sum: '$spent' },
                spentByTask: { $push: { task_id: '$_id', spent: '$spent' }
                }
            }
        },
        {
            $project: { _id: 0 }
        }
    ])

if you need a different result, let me know and i'll update my answer accordingly.

answered Mar 10, 2020 at 3:08

Dĵ ΝιΓΞΗΛψΚ

5,8044 gold badges16 silver badges30 bronze badges

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

Collectives™ on Stack Overflow

mongodb pipeline aggregation group results into array

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related