Using $sum to calculate the total value in a object, mongoose/mongodb

Question

I have this:

var actions = [
  project: 23123,
  title: 'Change of windows
  energySaving: {
      electricity: {
        lighting: 24324,
        equipment: 23423,
        fans: 234234,
        distribution: 234324,
        chiller: 234234,
        other: 234324
      },
      heating: {
        ventilation: 234324,
        shell: 23423,
        tapWater: 23423
      },
  }
]);

This is my query to get some fields from the actions collection:

mongoose.model('Action').find({project: project._id})
  .select('title description energySaving')

      .exec(function(err, actions){
      res.status(200).send(actions);
    });
  })

Instead of getting the entire "energySaving" property, I would like to replace this with a "totalEnergySaving" which is the sum of all the subobjects. Is it possible to use a aggregation to do this? If so, it's probably the $sum feature. Not really sure how though.

Answer 1

It's going to be pretty difficult to achieve aggregation with the current schema design as you have arbitrary field names. In order to do most kinds of queries and operations, you will need to redesign you schema to store your data like this:

{
    "_id" : ObjectId("54f46f18c36dcc206d0cec38"),
    "project" : 23123,
    "title" : "Change of windows",
    "energySaving" : [ 
        {
            "energy" : "electricity",
            "type" : "lighting",
            "value" : 24324
        }, 
        {
            "energy" : "electricity",
            "type" : "equipment",
            "value" : 24324
        }, 
        {
            "energy" : "electricity",
            "type" : "fans",
            "value" : 24324
        }, 
        {
            "energy" : "electricity",
            "type" : "distribution",
            "value" : 24324
        }, 
        {
            "energy" : "electricity",
            "type" : "chiller",
            "value" : 24324
        }, 
        {
            "energy" : "electricity",
            "type" : "other",
            "value" : 24324
        }, 
        {
            "energy" : "heating",
            "type" : "ventilation",
            "value" : 24324
        }, 
        {
            "energy" : "heating",
            "type" : "shell",
            "value" : 24324
        }, 
        {
            "energy" : "heating",
            "type" : "tapWater",
            "value" : 24324
        }
    ]
}

which you can then aggregate to get the final title, description and totalEnergySaving as follows:

db.collection.aggregate( [
    { $unwind: "$energySaving" },
    { 
       $group: {
          _id: {
             title: '$title',
             description: '$description' 
          },
          totalEnergySaving: { $sum: '$energySaving.value' } 
       } 
    },
    {
        $project: {
            _id: 0,
            title: '$_id.title',
            description: '$_id.description',
            totalEnergySaving: 1
        }
    }
]);

Result:

{
    "result" : [ 
        {
            "totalEnergySaving" : 218916,
            "title" : "Change of windows",
            "description" : "Detailed breakdown of energy savings"
        }
    ],
    "ok" : 1
}