Geek Answers Handbook

How to sort mongodb query results based on subdocuments

I have 2 documents in (student) database databases as shown 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 build a query to get the result below.

 { 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' } ] }

that is: a query to search for documents based on time and sort the results by 2 criteria

  • id ASC
  • by sub document ASC time
+4
source share
3 answers

You can use cursor.sort() to sort by multiple fields (mostly combos) at the same time, but I don't think it works when sorting by document or by subdocument at the same time. If you would sort by two different fields of the top document or by two different fields of the attached document, that would be nice, I think.

This way you can get a similar output using the aggregation structure. All you have to do is split the arrays of the subs field and then sort them.

You can do something like:

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

With the above code, you should get a similar result:

  { 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' } }
+6
source

Oddly, MongoDB provides no way to sort supporting documents by querying it. It seems you will need to update the natural order of the subdomains as shown below:

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

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

and then run the query to sort by ID itself:

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

You will get the result below.

 { "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 your situation. Below is the link:

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

+3
source

You can use cursor.sort(sort)

For Exapmle:

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

to sort the results using id in ascending order.

See the following link for details. http://docs.mongodb.org/manual/reference/method/cursor.sort/

0
source

More articles:


All Articles