Aggregation - MongoDB : sum different values by id

Question

i perform an aggregation on a collection but the result is not what expected:

db.getCollection('test').aggregate(
                          [
                           {$match: { "colx" : 8323, "id" : {$in:    [802, 801, 803, 805, 810, 806, 807, 808, 809, 816]}}},
                           {$group : {_id : { coly : "$coly" , id : "$id" } ,  total: {$sum: "$val"} } },
                         ])

the collection 'test' contains the fields coly, id, val and each id can be the same for different coly with different values and one id can exists more than once for the same 'coly'.

The purpose of the aggregation is to get the sum of the values ('val') by "id" grouped by the field 'coly'.

mongo version is 3.4.

any help please?

the result is :

/* 1 */
{
"_id" : {
    "coly" : 898,
    "id" : 801
},
"total" : 3355560
}

/* 2 */
{
"_id" : {
    "coly" : 898,
    "id" : 805
},
"total" : 2760139
}

the expected result :

{
"_id": 898,
"results": [{
        "id": 801,
        "total": 3355560
    },
    {
        "id": 805,
        "total": 2760139
    }
]
}

Mạnh Quyết Nguyễn Mạnh Quyết Nguyễn · Accepted Answer · 2018-08-28T16:10:41

Your words are so ambigous.

Base on your expected result, I think your query is to group by coly then group by id and calculate the total for each group:

db.test.aggregate([
   {$match: { "colx" : 8323, "id" : {$in: [802, ....]}}},
   {$group: {_id : { coly : "$coly" , id : "$id" } ,  total: {$sum: "$val"} } },
   {$group: {_id: '$_id.coly', items: {$push: {id: '$_id.id', total: '$total'}} } }
])

This will output:

{
"_id": 898,
"items": [
   {
    "id" : 801
    "total" : 3355560
   },
   {
    "id" : 805
    "total" : 2760139
   }
]
}

Aggregation - MongoDB : sum different values by id

1 Answers