How To Sort mongodb query results based on subdocuments

Question

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

by id ASC
by sub document time ASC

i need to do same , can you please share your solution ?

s4suryapal
– s4suryapal

2020-08-12 14:06:08 +00:00
Commented Aug 12, 2020 at 14:06 — s4suryapal
– s4suryapal, Commented Aug 12, 2020 at 14:06

tzik · Accepted Answer · 2014-02-07 18:31:00Z

7

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

answered Feb 7, 2014 at 18:31

tzik

97110 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Arun K Over a year ago

The intended solution was { 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' } ] }

tzik Over a year ago

Correct, it's been a while since I saw that, but you can group as a last step of the aggregation I wrote and use $addToSet to get the desired output. If you want, I can write the query...

s4suryapal Over a year ago

@tzik Please write complete query to get intended solution. I need to do same

s4suryapal Over a year ago

@tzik You can answer this question : [stackoverflow.com/questions/63378828/…

logeekal · Accepted Answer · 2014-04-23 10:47:54Z

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

turivishal · Accepted Answer · 2020-08-12 15:44:00Z

0

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

$unwind deconstruct array subs

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 to hide _id

  { $project: { _id: 0 } },

$sort by id in ascending order

  { $sort: { id: 1 } }
])

Playground

answered Aug 12, 2020 at 15:44

turivishal

36.4k7 gold badges48 silver badges69 bronze badges

Comments

Arun K · Accepted Answer · 2013-09-25 11:53:32Z

-2

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/

answered Sep 25, 2013 at 11:53

Arun K

2521 silver badge5 bronze badges

2 Comments

Naveen Subramani Over a year ago

How do i sort the sub documents which contains list items from that i need to sort the list based on inner key time in the list. thats what i needed most

Arun K Over a year ago

i think sorting sub documents is not possible stackoverflow.com/questions/3848814/…

Collectives™ on Stack Overflow

How To Sort mongodb query results based on subdocuments

4 Answers 4

4 Comments

Comments

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

4 Comments

Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related