Find MongoDB document with array containing the maximum occurrence of a specific value

To find a MongoDB document with an array containing the maximum occurrence of a specific value, use the aggregation pipeline with $filter, $size, and $group operators to count occurrences and identify the document with the highest count.

Syntax

db.collection.aggregate([
    { $project: {
        "arrayField": 1,
        "occurrenceCount": {
            $size: {
                $filter: {
                    input: "$arrayField",
                    as: "element",
                    cond: { $eq: ["$$element", specificValue] }
                }
            }
        }
    }},
    { $group: {
        "_id": "$occurrenceCount",
        "documents": { $push: "$$ROOT" }
    }},
    { $sort: { "_id": -1 } },
    { $limit: 1 }
])

Sample Data

db.countOccurrencesDemo.insertMany([
    {"ListOfValues": [65, 87, 89, 65, 67, 87, 87, 87]},
    {"ListOfValues": [102, 65, 87, 65, 89, 65, 89, 65, 89, 65]}
]);

{
    "acknowledged": true,
    "insertedIds": [
        ObjectId("..."),
        ObjectId("...")
    ]
}

Display all documents ?

db.countOccurrencesDemo.find();

{ "_id": ObjectId("5e06ef9325ddae1f53b621eb"), "ListOfValues": [65, 87, 89, 65, 67, 87, 87, 87] }
{ "_id": ObjectId("5e06efaa25ddae1f53b621ec"), "ListOfValues": [102, 65, 87, 65, 89, 65, 89, 65, 89, 65] }

Example: Find Document with Maximum Occurrences of Value 65

db.countOccurrencesDemo.aggregate([
    { $project: {
        "ListOfValues": 1,
        "OccurrencesValue": {
            $size: {
                $filter: {
                    input: "$ListOfValues",
                    as: "v",
                    cond: { $eq: ["$$v", 65] }
                }
            }
        }
    }},
    { $group: {
        "_id": "$OccurrencesValue",
        "MyValues": { $push: "$$ROOT" }
    }},
    { $sort: { "_id": -1 } },
    { $limit: 1 }
]);

{
    "_id": 5,
    "MyValues": [
        {
            "_id": ObjectId("5e06efaa25ddae1f53b621ec"),
            "ListOfValues": [102, 65, 87, 65, 89, 65, 89, 65, 89, 65],
            "OccurrencesValue": 5
        }
    ]
}

How It Works

• $filter identifies array elements matching the specific value (65)
• $size counts the filtered elements to get occurrence count
• $group groups documents by their occurrence count
• $sort orders by count in descending order (-1)
• $limit returns only the document with maximum occurrences

Conclusion

Use aggregation pipeline with $filter and $size to count specific value occurrences in arrays. The combination of $group, $sort, and $limit identifies the document with maximum occurrences efficiently.