aggregation framework - 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
}