CODEWITHSUNDEEP
mongodb
Naveen Subramani
Naveen Subramani

Reputation: 2184

How To Sort mongodb query results based on subdocuments

i am having 2 documents in score collections(student)databases like below in mongodb database. 

{ 
    id: 2, 
    type: 'newname', 
    subs: [
        { time: 20, val: 'b' },
        { time: 12, val: 'a' },
        { time: 30, val: 'c' }
    ] }, { 
    id: 1, 
    type: 'strs', 
    subs: [
        { time: 50, val: 'be' },
        { time: 1, val: 'ab' },
        { time: 20, val: 'cs' }
    ] }

How to construct a query to get the below result 

{ 
    id: 1, 
    type: 'strs', 
    subs: [
        { time: 1, val: 'ab' },
        { time: 20, val: 'cs' },
        { time: 50, val: 'be' }
    ]
},
{ 
    id: 2, 
    type: 'newname', 
    subs: [
        { time: 12, val: 'a' },
        { time: 20, val: 'b' },
        { time: 30, val: 'c' }
    ]
}

ie: a query for find the documents based on time and have to sort the results on 2 criteria

  1. by id ASC
  2. by sub document time ASC

Upvotes: 6

Views: 16810

Answers (4)

turivishal
turivishal

Reputation: 36094

It is possible when use in sequence $unwind > $sort > $group specialty for array,

db.collection.aggregate([
  { $unwind: "$subs" },
  • $sort subs.time in ascending (1) order
  { $sort: { "subs.time": 1 } },
  • $group by id, and re-construct subs array using $push objects and other fields remain using $first
  {
    $group: {
      _id: "$id",
      id: { $first: "$id" },
      type: { $first: "$type" },
      subs: { $push: "$subs" }
    }
  },

  { $project: { _id: 0 } },
  • $sort by id in ascending order
  { $sort: { id: 1 } }
])

Playground

Upvotes: 0

logeekal
logeekal

Reputation: 525

Strangely, MongoDB does not provide any method to sort sub documents by querying it. It seems you will have to update the natural sort order of the sub docs as shown below :

If these are the only 2 docs in your collection, write below query :

db.test.update( {}, {$push : {"subs" :{$each  : [] , $sort : {time : -1}}}})

and then run query to sort on ID itself :

db.test.find().sort({'id':1}).pretty()

You will get below output.

{                                                             

        "id" : 1,                                             
        "type" : "strs",                                      
        "subs" : [                                            
                {                                             
                        "time" : 50,                          
                        "val" : "be"                          
                },                                            
                {                                             
                        "time" : 20,                          
                        "val" : "cs"                          
                },                                            
                {                                             
                        "time" : 1,                           
                        "val" : "ab"                          
                }                                             
        ]                                                     
}                                                             
{                                                             

        "id" : 2,                                             
        "type" : "newname",                                   
        "subs" : [                                            
                {                                             
                        "time" : 30,                          
                        "val" : "c"                           
                },                                            
                {                                             
                        "time" : 20,                          
                        "val" : "b"                           
                },                                            
                {                                             
                        "time" : 12,                          
                        "val" : "a"                           
                }                                             
        ]                                                     
}

Hope it solves you situation. Below is the reference :

http://docs.mongodb.org/manual/reference/operator/update/sort/#sort-array-of-documents-by-a-field-in-the-documents

Upvotes: 3

tzik
tzik

Reputation: 971

You can use cursor.sort() to sort on multiple fields (basically a combo) at the same time but I don't think it works when sorting on both a document and a subdocument field at the same time. If you were to sort on two different fields of the top document or on two different fields of a subdocument then it would be fine i guess.

So you can get a similar output using the aggregation framework. All you have to do is basically break down the arrays of the subs field and then sort them.

You could do something like: 

db.col.aggregate({$unwind:'subs'}, {$sort:{id:1,'subs.time':1}});

With the above code you should get an output similar to this: 

 { 
    id: 1, 
    type: 'strs', 
    subs: 
        { time: 1, val: 'ab' }
},{ 
    id: 1, 
    type: 'strs', 
    subs: 
        { time: 20, val: 'cs' }
},{ 
    id: 1, 
    type: 'strs', 
    subs: 
        { time: 50, val: 'be' }
},{ 
    id: 2, 
    type: 'newname', 
    subs: 
        { time: 12, val: 'a' }
},{ 
    id: 2, 
    type: 'newname', 
    subs: 
        { time: 20, val: 'b' }
},{ 
    id: 2, 
    type: 'newname', 
    subs: 
        { time: 30, val: 'c' }
}

Upvotes: 7

Arun K
Arun K

Reputation: 252

You can use cursor.sort(sort)

For Exapmle:

db.collection.find().sort( { 'id': 1 } )

to sort the results using id in ascending order.

For more details refer the following link. http://docs.mongodb.org/manual/reference/method/cursor.sort/

Upvotes: -2

Related Questions