MongoDB query for counting the distinct values across all documents?

To count distinct values across all documents in MongoDB, use the aggregate() method with $unwind, $addToSet, and $size operators. This approach flattens arrays, groups unique values, and counts them.

Syntax

db.collection.aggregate([
    { $unwind: "$arrayField" },
    { $group: { _id: null, uniqueValues: { $addToSet: "$arrayField" } } },
    { $project: { _id: 0, count: { $size: "$uniqueValues" } } }
]);

Sample Data

db.demo718.insertMany([
    {
        "id": 101,
        "details": {
            "OtherDetails": ["Chris", "Mike", "Sam"],
            "GroupName": ["Group-1"],
            "Info": []
        }
    },
    {
        "id": 102,
        "details": {
            "OtherDetails": ["Chris", "David"],
            "GroupName": ["Group-1"],
            "Info": []
        }
    }
]);

{
    "acknowledged": true,
    "insertedIds": [
        ObjectId("5eaae25843417811278f5880"),
        ObjectId("5eaae25943417811278f5881")
    ]
}

Display Sample Data

db.demo718.find();

{ "_id": ObjectId("5eaae25843417811278f5880"), "id": 101, "details": { "OtherDetails": [ "Chris", "Mike", "Sam" ], "GroupName": [ "Group-1" ], "Info": [ ] } }
{ "_id": ObjectId("5eaae25943417811278f5881"), "id": 102, "details": { "OtherDetails": [ "Chris", "David" ], "GroupName": [ "Group-1" ], "Info": [ ] } }

Example: Count Distinct Values by Group

Count distinct "OtherDetails" values for each "GroupName" ?

db.demo718.aggregate([
    {
        $unwind: "$details.GroupName"
    },
    {
        $match: {
            "details.GroupName": "Group-1"
        }
    },
    {
        $unwind: "$details.OtherDetails"
    },
    {
        $group: {
            _id: "$details.GroupName",
            OtherDetailsUnique: {
                $addToSet: "$details.OtherDetails"
            }
        }
    },
    {
        $project: {
            _id: 0,
            GROUP_NAME: "$_id",
            OtherDetailsUniqueCount: {
                $size: "$OtherDetailsUnique"
            }
        }
    }
]);

{ "GROUP_NAME": "Group-1", "OtherDetailsUniqueCount": 4 }

How It Works

• $unwind flattens array elements into separate documents
• $match filters documents by specific criteria
• $addToSet collects unique values, eliminating duplicates
• $size counts the number of unique elements

Conclusion

Use MongoDB's aggregation pipeline with $unwind, $addToSet, and $size to efficiently count distinct values across documents. This approach handles arrays and provides accurate unique value counts.