Aggregation framework to get the name of students with test one score less than the total average of all the tests

To find students whose test one score is less than the total average of all tests, use MongoDB's aggregation framework with $project, $group, $filter, and $map operators to calculate averages and filter results.

Syntax

db.collection.aggregate([
    { $project: { Name: 1, Value1: 1, average: { $avg: ["$Value1", "$Value2", "$Value3"] } } },
    { $group: { _id: null, students: { $push: {Name: "$Name", Value1: "$Value1"} }, totalAverage: { $avg: "$average" } } },
    { $project: { filteredNames: { $map: { input: { $filter: { input: "$students", cond: { $lt: ["$$this.Value1", "$totalAverage"] } } }, in: "$$this.Name" } } } }
]);

Sample Data

db.demo432.insertMany([
    {
        "_id": 101,
        "Name": "David",
        "Value1": 67,
        "Value2": 87,
        "Value3": 78
    },
    {
        "_id": 102,
        "Name": "Sam",
        "Value1": 98,
        "Value2": 45,
        "Value3": 90
    }
]);

{ "acknowledged": true, "insertedIds": { "0": 101, "1": 102 } }

Verify Sample Data

db.demo432.find();

{ "_id": 101, "Name": "David", "Value1": 67, "Value2": 87, "Value3": 78 }
{ "_id": 102, "Name": "Sam", "Value1": 98, "Value2": 45, "Value3": 90 }

Aggregation Query

db.demo432.aggregate([
    {
        $project: {
            Name: '$Name',
            Value1: '$Value1',
            average: {
                $avg: ['$Value1', '$Value2', '$Value3']
            }
        }
    },
    {
        $group: {
            _id: null,
            NameValue1: {
                $push: {
                    "Name": "$Name",
                    "Value1": "$Value1"
                }
            },
            totalAverage: {
                $avg: '$average'
            }
        }
    },
    {
        $project: {
            lessthanAverageNames: {
                $map: {
                    input: {
                        $filter: {
                            input: "$NameValue1",
                            as: "out",
                            cond: {
                                $lt: ["$$out.Value1", "$totalAverage"]
                            }
                        }
                    },
                    as: "o",
                    in: "$$o.Name"
                }
            }
        }
    }
]);

{ "_id": null, "lessthanAverageNames": [ "David" ] }

How It Works

• Stage 1 ($project): Calculate individual student averages across all three test scores
• Stage 2 ($group): Group all students and compute the overall average of individual averages
• Stage 3 ($project): Filter students whose Value1 (test one score) is less than the total average using $filter and extract names using $map

Conclusion

Use MongoDB's aggregation pipeline to compare individual test scores against calculated averages. The combination of $avg, $filter, and $map operators enables complex filtering based on computed values across the entire dataset.