Ordering by count of filtered subdocument array elements

Question

I currently have a MongoDB collection that looks like so:

{
    {
        "_id": ObjectId,
        "user_id": Number,
        "updates": [
            {
                "_id": ObjectId,
                "mode": Number,
                "score": Number
            },
            {
                "_id": ObjectId,
                "mode": Number,
                "score": Number
            },
            {
                "_id": ObjectId,
                "mode": Number,
                "score": Number
            }
        ]
    }
}

I am looking to find a way to find the users with the largest number of updates per mode. For instance, if I specify mode 0, I want it to load the users in order of greatest number of updates with mode: 0.

Is this possible in MongoDB? It does not need to be a fast algorithm, as it will be cached for quite a while, and it will run asynchronously.

Answer 1

The fastest way would be to store a count for each "mode" within the document as another field, then you could just sort on that:

var update = { 
   "$push": { "updates": updateDoc },
};

var countDoc = {};
countDoc["counts." + updateDoc.mode] = 1;

update["$inc"] = countDoc;

Model.update(
    { "_id": id },
    update,
    function(err,numAffected) {

    }
);

Which would use $inc to increment a "counts" field for each "mode" value as a key for each "mode" pushed to the "updates" array. All the calculation happens on update, so it's fast and so is the query that can be applied with a sort on that value:

Model.find({ "updates.mode": 0 }).sort({ "counts.0": -1 }).exec(function(err,users) {

});

If you don't want to or cannot store such a field then the other option is to calculate at query time with .aggregate():

Model.aggregate(
    [
        { "$match": { "updates.mode": 0 } },
        { "$project": {
            "user_id": 1,
            "updates": 1,
            "count": {
                "$size": {
                    "$setDifference": [
                        { "$map": {
                            "input": "$updates",
                            "as": "el",
                            "in": {
                                "$cond": [
                                    { "$eq": [ "$$el.mode", 0 ] },
                                    "$$el",
                                    false
                                ]
                            }
                        }},
                        [false]
                    ]
                }
            }
        }},
        { "$sort": { "count": -1 } }
    ],
    function(err,results) {

    }
);

Which isn't bad since the filtering of the array and getting the $size is fairly effecient, but it's not as fast as just using a stored value.

The $map operator allows inline processing of the array elements which are tested by $cond to see if it returns a match or false. Then $setDifference removes any false values. A much better way to filter array content than using $unwind, which can slow things down significantly and should not be used unless your intent to to aggregate array content across documents.

But the better approach is to store the value for the count instead, since this does not require runtime calculation and can even use an index

Answer 2

I think this is a duplicate of this question:

Mongo find query for longest arrays inside object

The accepted answer seem to be doing exactly what you ask for.

db.collection.aggregate( [
  { $unwind : "$l" },
  { $group : { _id : "$_id", len : { $sum : 1 } } },
  { $sort : { len : -1 } },
  { $limit : 25 }
] )

just replace "$l" with "$updates".

[edit:] and you probably do not want the result limited to 25, so you should also get rid of the { $limit : 25 }