MongoDB: Updating an average in a document with 2 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 update each truck's average_grade by adding all the grade_numbers. The problem I'm having is that the grade_numbers I am trying to add are in an array within an array. I have tried using $unwind to unwind both trucks and grades arrays.

This is the query I have tried using:

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

Do I have to add something more to my query? I want to update ALL of the trucks.average_grades, since there a lot of them in the document I am 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 an update. First of all, I noticed that there are some {} missing around your grade object inside the grades array. You might want to double check that your document structure is as posted. Secondly, there are a couple of issues with your aggregation query.

1. The $avg operator works inside a $group clause, not a $project.
2. When you use $avg, you do not need to use $sum.
3. You want to average trucks.grades.grade.grade_number, which actually holds the numeric value of the grade. That is, you are missing grade between grades and grade_number.

If you resolve those issues, you get a query 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're using MongoDB version 2.6 or higher, the aggregate method will return a cursor. You can iterate through that cursor and update the documents accordingly.

In this example, I search for documents that have a particular truck_id inside their trucks array and proceed to update the average_grade with the one computed by the aggregation query. You can modify it to suit your needs. Combined with the aggregation query, the code looks like this.

// 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});
}