Perform group and distinct together in a single MongoDB query

To perform group and distinct operations together in MongoDB, use the $group aggregation stage with $addToSet operator. This approach groups documents while collecting distinct values from specified fields in a single query.

Syntax

db.collection.aggregate([
    {
        $group: {
            _id: null,
            distinctField1: { $addToSet: "$fieldName1" },
            distinctField2: { $addToSet: "$fieldName2" },
            aggregateField: { $min|$max|$sum: "$fieldName" }
        }
    }
]);

Sample Data

db.demo16.insertMany([
    {
        "StudentName": "Chris",
        "StudentSection": "A",
        "StudentAge": 23,
        "StudentMarks": 47
    },
    {
        "StudentName": "Bob",
        "StudentSection": "B",
        "StudentAge": 21,
        "StudentMarks": 85
    },
    {
        "StudentName": "Carol",
        "StudentSection": "A",
        "StudentAge": 26,
        "StudentMarks": 97
    }
]);

{
    "acknowledged": true,
    "insertedIds": [
        ObjectId("5e13827455d0fc6657d21f07"),
        ObjectId("5e13827555d0fc6657d21f08"),
        ObjectId("5e13827655d0fc6657d21f09")
    ]
}

Example: Group and Distinct Operation

Get distinct student names and sections while calculating minimum and maximum ages and marks ?

db.demo16.aggregate([
    {
        $group: {
            _id: null,
            StudentName: { $addToSet: "$StudentName" },
            StudentSection: { $addToSet: "$StudentSection" },
            StudentMinimumAge: { $min: "$StudentAge" },
            StudentMaximumAge: { $max: "$StudentAge" },
            StudentMinimumMarks: { $min: "$StudentMarks" },
            StudentMaximumMarks: { $max: "$StudentMarks" }
        }
    }
]);

{
    "_id": null,
    "StudentName": [
        "Carol",
        "Bob", 
        "Chris"
    ],
    "StudentSection": [
        "B",
        "A"
    ],
    "StudentMinimumAge": 21,
    "StudentMaximumAge": 26,
    "StudentMinimumMarks": 47,
    "StudentMaximumMarks": 97
}

Key Points

• $addToSet collects distinct values from the specified field into an array.
• _id: null groups all documents into a single group.
• Combine with other aggregation operators like $min, $max, $sum for additional calculations.

Conclusion

The $group stage with $addToSet efficiently combines grouping and distinct operations in a single MongoDB aggregation query. This approach eliminates the need for separate distinct queries while providing additional aggregation capabilities.