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.

If you're looking for a very hacky solution that works well when you're rushing around, you can use `{'l.0': { $exists: true } }` and keep increasing 0 to 1 to 2... until you find the documents with the most items. Although this won't help you if you want to sort from highest -> lowest. — Raphael, Jan 05 '23 at 19:03

Asya Kamsky · Accepted Answer · 2013-11-30T16:12:49.373

24

Use the aggregation framework. Here's how:

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

edited Nov 30 '13 at 16:12

answered Nov 24 '13 at 09:40

Asya Kamsky

41,784
5
109
133

Thanks! Exactly what I needed. I thought aggregation was the correct approach but wasn't sure how to utilize it properly. Much appreciated :-) – mike029 Nov 29 '13 at 02:34

score 2 · Answer 2 · edited May 23 '17 at 11:47

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.

score 1 · Answer 3 · answered May 14 '21 at 14:33

There is no straight approach to do this,

You can try adding size field in your document using $size,

$addFields to add new field total to get total elements in l array
$sort by total in descending order
$limit to select single document
$project to remove total field if you don't needed

db.collection.aggregate([
  { $addFields: { total: { $size: "$l" } } },
  { $sort: { total: -1 } },
  { $limit: 25 }
  // { $project: { total: 0 } }
])

Playground

Mongo find query for longest arrays inside object

3 Answers3

Linked