Sum different fields using aggregation in MongoDB

Question

I have a document schema in a test collection like this:

{
  "_id" : NumberLong("A unique number"),
  "text" : {
         "characters_map" : {
                          "a" : 4,
                          "f" : 3,
                          "b" : 6,
                            ...
                          "o" : 3
                         }
            ...
         }
    ...
}

I want to count the sum of each character in the whole collection. I tried using the aggregation framework, but it doesn't seem I do it correct.

I began by calculating each character separately ( i.e the 'a'), but with no luck. My best approach is:

> db.test.aggregate([
    { "$group" : {
         _id : { a_count : "$text.characters_map.a" },
         counter : { "$sum" : "$text.characters_map.a" }
      }
    }
  ])

with a_count representing the name of the id.

which results to:

{ "_id" : { "a_map" : 8 }, "counter" : 8 }
{ "_id" : { "a_map" : 5 }, "counter" : 5 }
{ "_id" : { "a_map" : 7 }, "counter" : 21 }
 ...

Not false entirely, but not what I want. These results mean I have 1 document with a field of: { "a" : 8, ... }, another 1 document with: { "a" : 5, ... } and 3 documents with: { "a" : 7, ... }.

Is there any way to group these fields together at "a" level? Is my approach completely wrong?

s7vr · Accepted Answer · 2017-04-26 21:24:18Z

2

You have to change your structure to something like below.(Preferred)

[{
  "text" : { "characters_map" : [{"k":"a", "v":4},  {"k":"b", "v":8},  {"k":"c", "v":5}, {"k":"d", "v":4  }]  }
}]

You can use aggregation the way you tried in your post.

db.test.aggregate([
 {$unwind:"$text.characters_map"},
 {$group:{_id:"$text.characters_map.k", counter:{$sum:"$text.characters_map.v"}}}
 ])

This is the solution if you can't change your structure.

You can use 3.4.4 version and use $objectToArray & $arrayToObject to switch between dynamic keys and label value pair.

Stage 1: Convert the dynamic characters_map keys into label value pair.

Stage 2 & 3: $unwind & $group on the characters and sum their counts.

Stage 4 & 5: Group back the output from last grouping into array of key value pairs followed by $arrayToObject to convert the key value pairs into dynamic keys.

db.test.aggregate([
 {$addFields: {"text.characters_map": {$objectToArray: "$text.characters_map"}}},
 {$unwind:"$text.characters_map"},
 {$group:{_id:"$text.characters_map.k", counter:{$sum:"$text.characters_map.v"}}},
 {$group:{_id:null, arraykeyval:{$push:{k:"$_id", v:"$counter"}}}},
 {$project:{result: {$arrayToObject:"$arraykeyval"}}}
 ])

answered Apr 26, 2017 at 21:24

s7vr

76.2k12 gold badges118 silver badges135 bronze badges

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

1 Comment

Skod Over a year ago

works like a charm!! Thanks a lot for the schema re-structure recommendation too, I didn't thought of it! Now it makes sense...

Collectives™ on Stack Overflow

Sum different fields using aggregation in MongoDB

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related