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.

@Michael9 So then I need to run two queries right? One to get the averages and one to update the averages? Do I have to store the averages on another "variable"? — suecarmol
– suecarmol, Commented Feb 27, 2015 at 15:45
Double nested arrays are not a good idea if you need to query the data inside them. They are ok if the data is just for display. Perhaps you should reconsider the data model? Maybe each truck should be its own document, with a company_id field? Or each grade its own document? You do not want to rely on a double unwind aggregation pipeline for a normal query, so I would not recommend the below solution unless the query is uncommon. — wdberkeley
– wdberkeley, Commented Feb 27, 2015 at 16:08
@wdberkeley Wouldn't that make it kind of like a relational database? I'm new to MongoDB, so this is the first time I've actually modeled a document database. I'm just using this query a couple of times (the actual calculation will be made in Hadoop). — suecarmol
– suecarmol, Commented Feb 27, 2015 at 16:38

suecarmol · Accepted Answer · 2015-02-27 16:46:55Z

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.

The $avg operator works inside a $group clause, not a $project.
When you use $avg, you do not need to use $sum.
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});
}

Collectives™ on Stack Overflow

MongoDB: Updating an average in a document with 2 nested arrays

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related