CODEWITHSUNDEEP
mongodbaggregation-frameworkdatabasenosql
dev777
dev777

Reputation: 1019

Perform aggregation in an array of embedded doc

"employees":[  
      {  
         "empId":100, 
         "Salary":[  
           1000,2000,3000
         ]
   },
   {  
         "empId":101, 
         "Salary":[  
           3000,4000,500
         ]
   }
   ]

In the above array, I need to aggregate the salary like 1000+3000,2000+4000 e.t.c and place it in the separate array.

My aggregated result should be : adding salaries of both empId's salary[0]+salray[0](empId:100 + empId:101=1000+3000,2000+4000,3000+500)

"employees":[  
      {  
         "empId":100, 
         "Salary":[  
           1000,2000,3000
         ]
   },
   {  
         "empId":101, 
         "Salary":[  
           3000,4000,500
         ]
   },
    {  
         "empId":111, 
         "Salary":[  
           4000,6000,3500
         ]
   }
   ]

Upvotes: 0

Views: 64

Answers (1)

profesor79
profesor79

Reputation: 9473

You need to $unwind all arrays and then group using aggregation framework

db.dev777.aggregate([{
            $unwind : "$employees"
        }, {
            $unwind : "$employees.Salary"
        }, {
            $group : {
                _id : "$employees.empId",
                salarySum : {
                    $sum : "$employees.Salary"
                }
            }
        }
    ])

OUTPUT:

{
    "_id" : 101.0,
    "salarySum" : 7500.0
},{
    "_id" : 100.0,
    "salarySum" : 6000.0
}

EDIT

db.dev777.aggregate([{
            // transform array to document
            $unwind : "$employees"
        }, {
            // transform array to document and add array index to preserve position info
            $unwind : {
                path : "$employees.Salary",
                includeArrayIndex : "arrayIndex"
            }
        },
        {
            $group : {
                // now sum all data by array index field 
                _id : "$arrayIndex",
                salarySum : {
                    $sum : "$employees.Salary"
                }
            }
        }, {
            $sort : {
                // sort by array index field
                _id : 1
            }
        }, {
            $group : {
                // recreate document by pushing back values to an array
                _id : null,
                Salary : {
                    $push : "$salarySum"
                }
            }
        }, {
            $project : {
                //remove id field and add empID field  
                _id : 0,
                 empID:  {
                    $literal : NumberInt(111)
                },
                Salary : 1
            }
        }
    ])

Upvotes: 1

Related Questions