How to sum every fields in a sub document of MongoDB?

Question

I got a problem when I use db.collection.aggregate in MongoDB.

I have a data structure like:

_id:...

Segment:{
  "S1":1,
  "S2":5,
  ...
  "Sn":10
}

It means the following in Segment: I might have several sub attributes with numeric values. I'd like to sum them up as 1 + 5 + .. + 10

The problem is: I'm not sure about the sub attributes names since for each document the segment numbers are different. So I cannot list each segment name. I just want to use something like a for loop to sum all values together.

I tried queries like:

db.collection.aggregate([

  {$group:{
    _id:"$Account",

    total:{$sum:"$Segment.$"}
])

but it doesn't work.

Answer 1

You have made the classical mistake to have arbitrary field names. MongoDB is "schema-free", but it doesn't mean you don't need to think about your schema. Key names should be descriptive, and in your case, f.e. "S2" does not really mean anything. In order to do most kinds of queries and operations, you will need to redesign you schema to store your data like this:

_id:...
Segment:[
    { field: "S1", value: 1 },
    { field: "S2", value: 5 },
    { field: "Sn", value: 10 },
]

You can then run your query like:

db.collection.aggregate( [
    { $unwind: "$Segment" },
    { $group: {
        _id: '$_id', 
        sum: { $sum: '$Segment.value' } 
    } } 
] );

Which then results into something like this (with the only document from your question):

{
    "result" : [
        {
            "_id" : ObjectId("51e4772e13573be11ac2ca6f"),
            "sum" : 16
        }
    ],
    "ok" : 1
}

Answer 2

Starting Mongo 3.4, this can be achieved by applying inline operations and thus avoid expensive operations such as $group:

// { _id: "xx", segments: { s1: 1, s2: 3, s3: 18, s4: 20 } }
db.collection.aggregate([
  { $addFields: {
      total: { $sum: {
        $map: { input: { $objectToArray: "$segments" }, as: "kv", in: "$$kv.v" }
      }}
  }}
])
// { _id: "xx", total: 42, segments: { s1: 1, s2: 3, s3: 18, s4: 20 } }

The idea is to transform the object (containing the numbers to sum) as an array. This is the role of $objectToArray, which starting Mongo 3.4.4, transforms { s1: 1, s2: 3, ... } into [ { k: "s1", v: 1 }, { k: "s2", v: 3 }, ... ]. This way, we don't need to care about the field names since we can access values through their "v" fields.

Having an array rather than an object is a first step towards being able to sum its elements. But the elements obtained with $objectToArray are objects and not simple integers. We can get passed this by mapping (the $map operation) these array elements to extract the value of their "v" field. Which in our case results in creating this kind of array: [1, 3, 18, 42].

Finally, it's a simple matter of summing elements within this array, using the $sum operation.