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
    }
]
}

Your expected result is not a valid JSON, please fix that

mickl
– mickl

2018-08-28 15:59:32 +00:00
Commented Aug 28, 2018 at 15:59 — mickl
– mickl, Commented Aug 28, 2018 at 15:59
correction done! thanks

med.tu
– med.tu

2018-08-28 16:11:58 +00:00
Commented Aug 28, 2018 at 16:11 — med.tu
– med.tu, Commented Aug 28, 2018 at 16:11

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

1

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
   }
]
}

answered Aug 28, 2018 at 16:10

Mạnh Quyết Nguyễn

18.3k1 gold badge28 silver badges54 bronze badges

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

Collectives™ on Stack Overflow

Aggregation - MongoDB : sum different values by id

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