CODEWITHSUNDEEP
mongodbsizesql-order-by
Evgenius
Evgenius

Reputation: 955

Mongo order by length of array

Lets say I have mongo documents like this:

Question 1

{
    answers:[
       {content: 'answer1'},
       {content: '2nd answer'}
    ]
}

Question 2

{
    answers:[
       {content: 'answer1'},
       {content: '2nd answer'}
       {content: 'The third answer'}
    ]
}

Is there a way to order the collection by size of answers?

After a little research I saw suggestions of adding another field, that would contain number of answers and use it as a reference but may be there is native way to do it?

Upvotes: 61

Views: 58064

Answers (4)

Balaji
Balaji

Reputation: 161

You can use $size attribute to order by array length.

db.getCollection('test').aggregate([
{$project: { "answers": 1, "answer_count": { $size: "$answers" } }},
{$sort: {"answer_count": -1}}])

Upvotes: 10

Oleg
Oleg

Reputation: 3200

I use $project for this:

db.test.aggregate([
    {
        $project : { answers_count: {$size: { "$ifNull": [ "$answers", [] ] } } }
    }, 
    {   
        $sort: {"answers_count":1} 
    }
])

It also allows to include documents with empty answers. But also has a disadvantage (or sometimes advantage): you should manually add all needed fields in $project step.

Upvotes: 53

bhavin jalodara
bhavin jalodara

Reputation: 1530

you can use mongodb aggregation stage $addFields which will add extra field to store count and then followed by $sort stage.

db.test.aggregate([
    {
        $addFields: { answers_count: {$size: { "$ifNull": [ "$answers", [] ] } } }
    }, 
    {   
        $sort: {"answers_count":1} 
    }
])

Upvotes: 19

Eve Freeman
Eve Freeman

Reputation: 33155

I thought you might be able to use $size, but that's only to find arrays of a certain size, not ordering.

From the mongo documentation: http://www.mongodb.org/display/DOCS/Advanced+Queries#AdvancedQueries-%24size

You cannot use $size to find a range of sizes (for example: arrays with more than 1 element). If you need to query for a range, create an extra size field that you increment when you add elements. Indexes cannot be used for the $size portion of a query, although if other query expressions are included indexes may be used to search for matches on that portion of the query expression.

Looks like you can probably fairly easily do this with the new aggregation framework, edit: which isn't out yet. http://www.mongodb.org/display/DOCS/Aggregation+Framework

Update Now the Aggregation Framework is out...

> db.test.aggregate([
  {$unwind: "$answers"}, 
  {$group: {_id:"$_id", answers: {$push:"$answers"}, size: {$sum:1}}}, 
  {$sort:{size:1}}]);
{
"result" : [
    {
        "_id" : ObjectId("5053b4547d820880c3469365"),
        "answers" : [
            {
                "content" : "answer1"
            },
            {
                "content" : "2nd answer"
            }
        ],
        "size" : 2
    },
    {
        "_id" : ObjectId("5053b46d7d820880c3469366"),
        "answers" : [
            {
                "content" : "answer1"
            },
            {
                "content" : "2nd answer"
            },
            {
                "content" : "The third answer"
            }
        ],
        "size" : 3
    }
  ],
  "ok" : 1
}

Upvotes: 60

Related Questions