Mongo find query for longest arrays inside object

Question

I currently have objects in mongo set up like this for my application (simplified example, I removed some irrelevant fields for clarity here):

{
    "_id" : ObjectId("529159af5b508dd71500000a"),
    "c" : "somecontent",
    "l" : [
        {
            "d" : "2013-11-24T01:43:11.367Z",
            "u" : "User1"
        },
        {
            "d" : "2013-11-24T01:43:51.206Z",
            "u" : "User2"
        }
     ]
}

What I would like to do is run a find query to return the objects which have the highest array length under "l" and sort highest->lowest, limit to 25 results. Some objects may have 1 object in the array, some may have 100. I'd like to find out which ones have the most under "l". I'm new to mongo and got everything else to work up until this point, but I just can't figure out the right parameters to get this specific query. Where I'm getting confused is how to handle counting the length of the array, sorting, etc. I could manually code this by parsing everything in the collection, but I'm sure there has to be a way for mongo to do this far more efficiently. I'm not against learning, if anyone knows any resources for more advanced queries or could help me out I'd really be thankful as this is the last piece! :-)

As a side note, node.js and mongo together is amazing and I wish I started using them in conjunction a long time ago.

Answer 1

Use the aggregation framework. Here's how:

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

Answer 2

There is no easy way to do this with your existing schema. The reason for this is that there is nothing in mongodb to find the size of your array length. Yes, you have $size operator, but the way it works is just to find all the arrays of a specific length.

So you can not sort your find query based on the length of the array. The only reasonable way out is to add additional field to your schema which will hold the length of the array (you will have something like "l_length : 3" in additional to your fields for every document). Good thing is that you can do it easily by looking at this relevant answer and after this you just need to make sure to increment or decrement this value when you are modifying the array.

When you will add this field, you can easily sort it by that field and moreover you can take advantage of indexes.