Summation of sub document field based on a group unexpected behaviour

Question

*I have a document structured in which we embedded the salary Document into  the Employee_Detail Document.As per the MongoDb documantation ,we can use $Unwind to deconstruct the Document and use aggregated pipeline...But its not working. i am using the below script...*    
{ 
    "_id" : ObjectId("5763d4a54da83b98f269878a"),  
    "First_Name" : "fgfg", 
    "Department" : "QA", 
    "Salary" : {
        "HRA" : "1200", 
        "Basic" : "2000", 
    }

})  
And i want to get sum of basic salary based on department Like

then Expected output is Department Total_Basic** QA 2000

I have used the following code to get the output. I have used the $unwind to     deconstruct the document.and use aggregated pipeline to group the department(Sum of basic Salary).

db.Employee_Detail.aggregate([
    {$unwind:"$Salary"},   {$group: {"_id": "$Department", total_Basic: {$sum: "$Salary.Basic" }
    }}
])    
But i get the below Result.   
Department    Total_Basic
QA             0

I think $unwind is not Working. Please suggest

score 0 · Accepted Answer · edited May 23 '17 at 12:08

Your main problem is the type of the field Basic is a string. Second, you do not need to use unwind unless the field Salary contains an array.

So perform an update to convert the types of Basic and HRA to floats, (see this stackoverflow question)

And then an aggregate operation like this will give you the desired result:

db.Employee_Detail.aggregate([
    {$group: {"_id": "$Department", total_Basic: {$sum: "$Salary.Basic" }}
])

Summation of sub document field based on a group unexpected behaviour

1 Answers1