59

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?

Evgenius
  • 935
  • 1
  • 8
  • 17

4 Answers4

58

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
}
Eve Freeman
  • 32,467
  • 4
  • 86
  • 101
  • 1
    seems that result loose documents without answers array. sometimes its ok ofcourse. – Oleg Jan 12 '15 at 13:18
  • Is there a way to get the document without answers? I like the `$project` way also as answered by @OlegGolovanov but as he rightly mentioned, you have to manually add all needed fields is something which I dont like – Jay Dec 03 '15 at 14:25
51

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.

Masoud Salehi
  • 62
  • 9
Oleg
  • 3,080
  • 3
  • 40
  • 51
  • 7
    Since 3.4, mongo offers the `$addFields` stage as an alternative to `$project` in case you want to include all fields of the source document. https://docs.mongodb.com/v3.4/reference/operator/aggregation/addFields/ – dokkaebi Dec 04 '18 at 17:19
14

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} 
    }
])
bhavin jalodara
  • 1,470
  • 9
  • 12
8

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}}])
Balaji
  • 141
  • 1
  • 3