MongoDB (mongoose) aggregate count instances of specific ObjectIDs in collection

Question

Assuming I have a schema that looks something like this:

{
    field: [{
        subDoc: ObjectId,
        ...
    }],
    ...
}

and I have some list of ObjectIds (user input), how would I get a count of those specific ObjectIds? For exmaple, if I have data like this:

[
    {field: [ {subDoc: 123}, {subDoc: 234} ]},
    {field: [ {subDoc: 234}, {subDoc: 345} ]},
    {field: [ {subDoc: 123}, {subDoc: 345}, {subDoc: 456} ]}
]

and the list of ids given by the user is 123, 234, 345, I need to get a count the given ids, so a result approximating this:

{
    123: 2,
    234: 2,
    345: 2
}

What would be the best way to go about this?

Answer 1

The aggregation framework itself if not going to dynamically name keys the way you have presented as a proposed output, and that probably is a good thing really. But you can probably just do a query like this:

db.collection.aggregate([
    // Match documents that contain the elements
    { "$match": { 
        "field.subDoc": { "$in": [123,234,345] }
    }},

    // De-normalize the array field content
    { "$unwind": "$field" },

    // Match just the elements you want
    { "$match": { 
        "field.subDoc": { "$in": [123,234,345] }
    }},

    // Count by the element as a key
    { "$group": {
        "_id": "$field.subDoc",
        "count": { "$sum": 1 }
    }}
])

That gives you output like this:

{ "_id" : 345, "count" : 2 }
{ "_id" : 234, "count" : 2 }
{ "_id" : 123, "count" : 2 }

But if you really want to go nuts on this, you are specifying the "keys" that you want as part of your query, so you could form a pipeline like this:

db.collection.aggregate([
    { "$match": { 
        "field.subDoc": { "$in": [123,234,345] }
    }},
    { "$unwind": "$field" },
    { "$match": { 
        "field.subDoc": { "$in": [123,234,345] }
    }},
    { "$group": {
        "_id": "$field.subDoc",
        "count": { "$sum": 1 }
    }},
    { "$group": {
        "_id": null,
        "123": { 
            "$max": {
                "$cond": [
                    { "$eq": [ "$_id", 123 ] },
                    "$count",
                    0
                ]
            }
        },
        "234": { 
            "$max": {
                "$cond": [
                    { "$eq": [ "$_id", 234 ] },
                    "$count",
                    0
                ]
            }
        },
        "345": { 
            "$max": {
                "$cond": [
                    { "$eq": [ "$_id", 345 ] },
                    "$count",
                    0
                ]
            }
        }
    }}
])

Which is a relatively simple thing to construct that last stage in code by just processing the list of arguments:

var list = [123,234,345];

var group2 = { "$group": { "_id": null } };

list.forEach(function(id) {
    group2["$group"][id] = { 
        "$max": {
            "$cond": [
                { "$eq": [ "$_id", id ] },
                "$count",
                0
            ]
        }
    };
});

And that comes out more or less how you want it.

{ 
    "_id" : null, 
    "123" : 2, 
    "234" : 2, 
    "345" : 2 
}

Answer 2

Not exactly what you're asking for but it can give you an idea:

    db.test.aggregate([
    {
        $unwind: '$field'
    },
    {
        $group: {
            _id: {
                subDoc: '$field.subDoc'
            },
            count: {
                $sum: 1
            }
        }
    },
    {
        $project: {
            subDoc: '$subDoc.subDoc',
            count: '$count'
        }
    }
]);

Output:

 {
    "result": [
        {
            "_id": {
                "subDoc": 456
            },
            "count": 1
        },
        {
            "_id": {
                "subDoc": 345
            },
            "count": 2
        },
        {
            "_id": {
                "subDoc": 234
            },
            "count": 2
        },
        {
            "_id": {
                "subDoc": 123
            },
            "count": 2
        }
    ],
    "ok": 1
}