mongodb

sql-order-by

size

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?

Solution 1

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
}

Solution 2

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.

Solution 3

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

Solution 4

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