CODEWITHSUNDEEP
mongodbnosql
Dustin
Dustin

Reputation: 15

Construct count query in MongoDB

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.

Upvotes: 1

Views: 2197

Answers (2)

Gary Murakami
Gary Murakami

Reputation: 3402

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.

  1. 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!

  2. Aggregation "group" - http://www.mongodb.org/display/DOCS/Aggregation

  3. 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

Upvotes: 0

Asya Kamsky
Asya Kamsky

Reputation: 42342

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}})

Upvotes: 2

Related Questions