Rohit Lala Rohit Lala - 21 days ago 8
Javascript Question

MongoDB aggregate for Arithmetic Operations - Subdocument Field

We have a aggregate query projecting few sub documents. We wanted to apply a few arithmetic operations like Sum and Product on these projected values.

Aggregation Query --

Item.aggregate([
{ $unwind: '$dummy'},
{ $match: {'dummy.storage': {$gt: 0}} },
{ $group: {_id: '$_id',
dummy: {$push: '$dummy'},
original_y: { $first: "$original_y" },
new_y: { $first: "$new_y" },

}},
{$project:{
original_y: 1, new_y: 1,
tallyAmount: {$sum: ["$new_y","$original_y"] }
}
},
]
)
.exec(function(err, results){
if(err)
{
console.log("Error : " + err);
return res.json ({error: "Error"});

}
else if(!(results) || results == null || results.length == 0)
{
console.log("No Results Found");
return res.json ({error: "No Results Today"});

}else{

res.send(results);
}
});


This gives an error stating
invalid operator '$sum'


What should we do to get the sum of
original_y
and
new_y
in the
$project
?

EDIT

Documents:

{
id:1,
original_y: 200,
new_y: 140,
dummy: [
{id:1, storage:2, cost: 10},
{id:2, storage:0, cost: 20},
{id:3, storage:5, cost: 30},
]
}


OUTPUT EXPECTED:

{
id:1,
original_y: 200,
new_y: 140,
dummy: [
{id:1, storage:2, cost: 10, tallyAmount: 34},
{id:3, storage:5, cost: 30, tallyAmount: 11.33},
]
}


where,
tallyAmount = (original_y + new_y) / cost


ERROR: can't add an expression for a subfield of dummy because there is already an expression that applies to the whole field

Answer

Without much details of your document schema and the expected aggregation result, I would suggest you try the following aggregation as I believe you need the $add operator rather than the $sum operator. Note that the $sum operator is only applicable to the $group operator. With the $add operator, it adds up two numbers/fields together and the result is stored in a new field with the $project operator:

Item.aggregate([
    { "$match": { "dummy.storage": { "$gt": 0 } } },
    { "$unwind": "$dummy" },   
    { "$group": { 
        "_id": "$_id", 
        "original_y": { "$first": "$original_y" },
        "new_y": { "$first": "$new_y" }
    } },
    { "$project": {
        "original_y": 1, 
        "new_y": 1,
        "tallyAmount": { "$add": [ "$new_y", "$original_y" ] }
    } }
]).exec(callback);

-- UPDATE --

To satisfy the condition tallyAmount = (original_y + new_y) / cost, you should use the $add and $divide arithmetic operators in your $project operator pipeline stage thus your final aggregation pipeline would look like this:

Item.aggregate([
    { "$match": { "dummy.storage": { "$gt": 0 } } },
    { "$unwind": "$dummy" },
    {
        "$project": {
            "original_y": 1, 
            "new_y": 1,
            "dummy.id": "$dummy.id",
            "dummy.storage": "$dummy.storage",
            "dummy.cost": "$dummy.cost",
            "dummy.tallyAmount": {
                "$divide": [
                    { "$add": ["$new_y","$original_y"] },
                    "$dummy.cost"
                ]
            }
        }
    },
    {
        "$group": {
            "_id": "_$id",
            "original_y": { "$first": "$original_y" },
            "new_y": { "$first": "$new_y" },
            "dummy": {
                "$addToSet": "$dummy"
            }
        }        
    }
 ]).exec(callback);