MongoDB: updating the average value in a document using two nested arrays

Question

I have the following MongoDB document:

{ _id: ObjectId(), company_name: "Name", registered: 2/21/2015 2:00, trucks: [ { truck_id: "TEB7622", weight: 88.33, capacity: 273.333, length: 378.333, width: 377.383, average_grade: 2.5, grades: [ { grade_number: 4, timestamp: 2/21/2015 2:00 } ] }, { truck_id: "TEB5572", weight: 854.33, capacity: 2735.333, length: 378.333, width: 37.383, average_grade: 3.8, grades: [ { grade_number: 4, timestamp: 2/21/2015 2:00 } ] } ] } 

I want to upgrade each average_grade truck by adding all grade_numbers . The problem I am facing is that the grade_numbers I'm trying to add is in an array inside the array. I tried using $unwind to unwind both trucks and sort classes.

This is the query I tried to use:

 db.col.aggregate([ {$unwind: "$trucks"}, {$unwind: "$trucks.grades"}, { $project: { "_id": "$trucks.truck_id", "trucks.average_grade": { $avg: { $sum: "trucks.grades.grade_number"} } } }]) 

Should I add something else to my request? I want to update ALL trucks.average_grades , since there are many of them in the document I'm trying to update.

Answer 1

You cannot use aggregation to update a document, but you can definitely use it to get the data you want to use for updating. First of all, I noticed that there is no {} in your grade object inside the grades array. You might want to double check that your document structure is published. Secondly, there are several problems with your aggregation request.

• The $avg statement works inside the $group clause, not the $project clause.
• When you use $avg you do not need to use $sum .
• You want to average trucks.grades.grade.grade_number , which actually contains the numerical value of the class. That is, you are missing grade between grades and grade_number .

If you solve these problems, you will receive a request similar to the following:

 db.col.aggregate([ { "$unwind": "$trucks" }, { "$unwind": "$trucks.grades" }, { "$group": { "_id": "$trucks.truck_id", "average_grade": { "$avg": "$trucks.grades.grade_number" } } } ]); 

For your sample document that returns:

 { "_id" : "TEB5572", "average_grade" : 4 } { "_id" : "TEB7622", "average_grade" : 4 } 

Now you can use this information to update the average_grade field. If you are using MongoDB version 2.6 or higher, the aggregate method will return the cursor. You can scroll this cursor and update documents accordingly.

In this example, I am looking for documents that have a specific truck_id inside their trucks array, and proceed with updating average_grade using the method calculated by aggregation request. You can change it according to your needs. In combination with an aggregation request, the code is as follows.

 // Get average grade for each truck and assign results to cursor. var cur = db.col.aggregate([ { "$unwind": "$trucks" }, { "$unwind": "$trucks.grades" }, { "$group": { "_id": "$trucks.truck_id", "average_grade": { "$avg": "$trucks.grades.grade_number" } } } ]); // Iterate through results and update average grade for each truck. while (cur.hasNext()) { var doc = cur.next(); db.col.update({ "trucks.truck_id": doc._id }, { "$set": { "trucks.$.average_grade": doc.average_grade }}, { "multi": true}); }