7

How can I sort a query by the number of elements of an array field?

Let say I have records like

{
 title: '',
 author: '',
 votes: [id,id,id]
}

I would like to do sort by the length of the array votes

zishe
  • 10,665
  • 12
  • 64
  • 103
nachoab
  • 1,908
  • 1
  • 23
  • 36
  • Have you tried size? Example db.collection.find( { field: { $size: 1 } } ); – Arihant Godha May 30 '14 at 11:41
  • $ size not fit, use aggregation framework. It s a duplicate : https://www.google.fr/url?sa=t&source=web&rct=j&ei=1XGIU5npNKSr0QW9x4CACw&url=http://stackoverflow.com/questions/9040161/mongo-order-by-length-of-array&cd=1&ved=0CC0QFjAA&usg=AFQjCNFVRKglH5J4WUHAPRrcM9giudgTGg&sig2=BzLFnwF4MXFemfIwAQv1CQ – kranteg May 30 '14 at 11:57
  • @kranteg Don't see your duplicate vote. Or even a valid duplicate. **`$size`** is such a new introduction and there are also other valid solutions if people have not yet progressed to MongoDB 2.6 or greater yet. – Neil Lunn May 30 '14 at 12:18
  • @NeilLunn I made a duplicate vote with this link http://stackoverflow.com/questions/9040161/mongo-order-by-length-of-array but it seems I failed. I didn't know we can use $size like this. And you are right, solution depends on mongoDB version. – kranteg May 30 '14 at 12:34

3 Answers3

20

Use the aggregation framework with help from the $size operator from MongoDB 2.6 and upwards:

db.collection.aggregate([
    // Project with an array length
    { "$project": {
        "title": 1,
        "author": 1,
        "votes": 1,
        "length": { "$size": "$votes" }
    }},

    // Sort on the "length"
    { "$sort": { "length": -1 } },

    // Project if you really want
    { "$project": {
        "title": 1,
        "author": 1,
        "votes": 1,
    }}
])

Simple enough.

If you do not have a version 2.6 available you can still do this with a little more work:

db.collection.aggregate([
    // unwind the array
    { "$unwind": "$votes" },

    // Group back
    { "$group": {
        "_id": "$id",
        "title": { "$first": "$title" },
        "author": { "$first": "$author" },
        "votes": { "$push": "$votes" },
        "length": { "$sum": 1 }
    }},

    // Sort again
    { "$sort": { "length": -1 } },

    // Project if you want to
    { "$project": {
        "title": 1,
        "author": 1,
        "votes": 1,
    }}
])

That is pretty much it.

Neil Lunn
  • 148,042
  • 36
  • 346
  • 317
3

With regular queries, you can only sort matched documents by field values. Aggregation queries will allow you to calculate the size of the array and sort by that values. Downside of using aggregation is that it can be slow.

If you're can't use aggregation, there is a workaround solution. You can create a field (e.g. voteCount) where you will store the size of your votes array. You can then create an index on that field and sort your documents by it. The upside of this approach is that the query will be fast. The downside of this approach is that you will manually have to keep count of the number of items in your array.

Christian P
  • 12,032
  • 6
  • 60
  • 71
-1

Try this:

db.collection.find().sort({votes:-1})

This will find all the posts and then sort the posts by the Votes. Here Votes is an array, so it will sort according to the array length in decreasing order (most liked on the top) because of the -1 inside sort({votes:-1}).

rohit
  • 24
  • 5