dratewka dratewka - 24 days ago 9
JSON Question

MongoDB - Calculate an average from nested arrays

I'm new to MongoDB and I'm trying to create a function to calculate average marks for a student.

I've created the below document:

student = {
"id": 123456,
"name": "John",
"surname": "Smith",
"marks":
{
"a": [1,5,6,1],
"b": [1,1,2,4],
"c": [3,5]}

};


I know how to calculate it for a specific object - let's say "a", but I have no idea how to merge these 3 objects to get the average for all objects (10 marks).

Below is my code for a specific object:

average = function (id,subj)
{
result = [];

student = db.students.findOne({ "id": 1234567});
temp_array = student.marks[subj];
var total = 0;

for(var i = 0; i<temp_array.length; i++)
{
total += temp_array[i];
}

average = total / temp_array.length;
result.push("The average is " + average);
return result;
};


Any help would be greatly appreciated!

Answer Source

Use an aggregation with $avg in a $project stage.

This will give you the average for each array a, b and c :

db.students.aggregate([{
    $match: {
        "id": "1234567"
    }
}, {
    $project: {
        a: { $avg: "$marks.a" },
        b: { $avg: "$marks.b" },
        c: { $avg: "$marks.c" }
    }
}])

This will gives you the average of combined array a, b and c :

db.students.aggregate([{
    $match: {
        "id": "1234567"
    }
}, {
    $group: {
        _id: 1,
        all: {
            $push: {
                marks: ["$marks.a", "$marks.b", "$marks.c"]
            }
        }
    }
}, {
    $unwind: "$all"
}, {
    $unwind: "$all.marks"
}, {
    $unwind: "$all.marks"
}, {
    $group: {
        _id: 1,
        marks: { $avg: "$all.marks" }
    }
}])

The first $group stage is used to push all arrays into another one called all. We need to $unwind this array and marks should be unwinded twice to obtain one value per document before the last $group stage where average is computed