Count by multiple fields with MongoDB aggregation

To count by multiple fields in MongoDB, use the $facet operator combined with $group and $sum. The $facet processes multiple aggregation pipelines within a single stage on the same set of input documents.

Syntax

db.collection.aggregate([
    {
        $facet: {
            "pipeline1": [ /* aggregation stages */ ],
            "pipeline2": [ /* aggregation stages */ ]
        }
    },
    /* additional processing stages */
]);

Sample Data

db.demo721.insertMany([
    {
        "details1": { "id": 101 },
        "details2": { "id": 101 },
        "details3": { "id": 101 }
    },
    {
        "details1": { "id": 101 },
        "details2": { "id": 102 },
        "details3": { "id": 102 }
    }
]);

{
    "acknowledged": true,
    "insertedIds": [
        ObjectId("5eaaebdd43417811278f5887"),
        ObjectId("5eaaebe943417811278f5888")
    ]
}

Verify Sample Data

db.demo721.find();

{ "_id": ObjectId("5eaaebdd43417811278f5887"), "details1": { "id": 101 }, "details2": { "id": 101 }, "details3": { "id": 101 } }
{ "_id": ObjectId("5eaaebe943417811278f5888"), "details1": { "id": 101 }, "details2": { "id": 102 }, "details3": { "id": 102 } }

Count by Multiple Fields

db.demo721.aggregate([
    {
        $facet: {
            ids: [
                {
                    $group: {
                        _id: null,
                        d3: { $addToSet: "$details3.id" },
                        d2: { $addToSet: "$details2.id" },
                        d1: { $addToSet: "$details1.id" }
                    }
                },
                {
                    $project: {
                        _id: 0,
                        listofall: { $setUnion: ["$d1", "$d2", "$d3"] }
                    }
                }
            ],
            d: [
                {
                    $project: {
                        _id: 0,
                        a1: "$details1.id",
                        a2: "$details2.id",
                        a3: "$details3.id"
                    }
                }
            ]
        }
    },
    { $unwind: "$d" },
    { $unwind: "$ids" },
    { $unwind: "$ids.listofall" },
    {
        $group: {
            _id: "$ids.listofall",
            details1id: { $sum: { $cond: [{ $eq: ["$d.a1", "$ids.listofall"] }, 1, 0] } },
            details2id: { $sum: { $cond: [{ $eq: ["$d.a2", "$ids.listofall"] }, 1, 0] } },
            details3id: { $sum: { $cond: [{ $eq: ["$d.a3", "$ids.listofall"] }, 1, 0] } }
        }
    }
]);

{ "_id": 102, "details1id": 0, "details2id": 1, "details3id": 1 }
{ "_id": 101, "details1id": 2, "details2id": 1, "details3id": 1 }

How It Works

• The $facet stage creates two parallel pipelines: ids and d
• ids pipeline collects all unique values from the three detail fields
• d pipeline extracts the field values for comparison
• $unwind operations flatten the arrays for processing
• Final $group counts occurrences of each ID across all three fields

Conclusion

Use $facet with $group and conditional counting to analyze multiple fields simultaneously. This approach efficiently counts field occurrences across different document properties in a single aggregation pipeline.