Construct count query in MongoDB

Question

I have a MongoDB database with documents that look like this:

{"users" : ["u1", "u2", "u3"]}
{"users" : ["u1", "u4"]}
{"users" : ["u1", "u3", "u5", "u6", "u7"]}

I would like to obtain the count of the document with the larger number of users. Using the above, the query would return 5 as the highest user count within the database. How can I do this in MongoDB?

I can get the number of documents with a specific size with:

db.mydb.find({users: {$size: 5}}).count()

However, I can not figure out how to find the largest count within all the documents in the user array.

Thanks.

Asya Kamsky · Accepted Answer · 2012-06-07 02:30:31Z

2

You can't do this directly in MongoDB. However, what you can do is have an extra field in the same document called "user_count" and use the $inc operator to increment it by one every time you add a new user to the "users" array.

Your update would look something like:

db.mydb.update({<update_condition>}, {$push :{"users":"u8"}, $inc : {"user_count":1}})

answered Jun 7, 2012 at 2:30

Asya Kamsky

42.4k5 gold badges113 silver badges133 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Gary Murakami · Accepted Answer · 2012-06-08 04:54:13Z

Below are three working solutions in MongoDB, however Asya's answer may be more appropriate, depending on your engineering needs. You should answer questions like: How big is my collection, and what's the cost/efficiency for each potential solution? Are there any other performance issues? Can I solve this more efficiently by book keeping in the problem domain?

You should consider tracking the max count explicitly via your application if this is a common question. For increase in users, you just track the max. But (only) if there is a decrease in a doc from the current max, you have to pay the cost for a full query. Asya's solution may still help with this full query.

Here are the MongoDB possibilities. Please examine the documentation carefully with respect to your problem and performance or engineering impact.

db.eval server-size code execution - http://www.mongodb.org/display/DOCS/Server-side+Code+Execution

CAUTION: Use map/reduce instead of db.eval() for long running jobs. db.eval blocks other operations!
Aggregation "group" - http://www.mongodb.org/display/DOCS/Aggregation
MapReduce - http://www.mongodb.org/display/DOCS/MapReduce

count_query.js

db.mydb.drop();
db.mydb.save({"users" : ["u1", "u2", "u3"]});
db.mydb.save({"users" : ["u1", "u4"]});
db.mydb.save({"users" : ["u1", "u3", "u5", "u6", "u7"]});
//printjson(db.mydb.find().toArray());

printjson(db.eval(function() {
    var count = 0;
    db.mydb.find().forEach( function(doc) {
        if (doc.users.length > count)
            count = doc.users.length;
    });
    return count;
}));

printjson(db.mydb.group({
    reduce: function(doc, prev) {
        if (doc.users.length > prev.count)
            prev.count = doc.users.length;
    },
    initial: {count: 0}
})[0].count);

printjson(db.mydb.mapReduce(
    function() { emit(0, {count: this.users.length}); },
    function(key, values) {
        var result = {count: 0};
        values.forEach(function(value) {
            if (value.count > result.count)
                result.count = value.count;
        });
        return result;
    },
    {out: {inline: 1}}  
).results[0].value.count);

execution and output

$ mongo count_query.js
MongoDB shell version: 2.0.4
connecting to: test
5
5
5

Collectives™ on Stack Overflow

Construct count query in MongoDB

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related