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.

score 1 · Accepted Answer · answered Oct 27 '15 at 00:35

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

score 0 · Answer 2 · edited May 23 '17 at 12:03

0

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 }

edited May 23 '17 at 12:03

Community

1
1

answered Oct 27 '15 at 00:31

80prozent

147
9

That's not very efficient as there are better ways to count arrays such as with `$size`. Nor do you addresss the question which asks for a "filtered count" for a specific "mode" value. There are also much more efficient ways, such as maintaining the count for each "mode" within the document itself as items are added. – Blakes Seven Oct 27 '15 at 00:41
The more crucial aspect is the filtering by mode. Are you able to apply that to this solution? – RedbackThomson Oct 27 '15 at 01:21
@Redback93 The point of the commentary was that this is not a solution, and that there was in fact another answer present at the time that shows how to get what you want with two effecient approaches. – Blakes Seven Oct 27 '15 at 09:04

Ordering by count of filtered subdocument array elements

2 Answers2