0

I would like to ranked in descending order a list of documents in array names via their number value.

Here's the structure part of my collection :

_id: ObjectId("W")
var1: "X",
var2: "Y",
var3: "Z",
comments: {
   names: [
      {
         number: 1;
      },
      {
         number: 3;
      },
      {
         number: 2;
      }
   ],
   field: Y;
}

but all my request with db.collection.find().sort( { "comments.names.number": -1 } ) doesn't work.

the desired output sort is :

{ "_id" : ObjectId("W"), "var1" : "X", "var3" : "Z", "comments" : { [ { "number" : 3 }, { "number" : 2 },{ "number" : 1 } ], "field": "Y" } }

Can you help me?

John S
  • 231
  • 3
  • 11

3 Answers3

1

You need to aggregate the result, as below:

  • Unwind the names array.
  • Sort the records based on comments.names.number in descending order.
  • Group the records based on the _id field.
  • project the required structure.

Code:

 db.collection.aggregate([
 {$unwind:"$comments.names"},
 {$sort:{"comments.names.number":-1}},
 {$group:{"_id":"$_id",
          "var1":{$first:"$var1"},
          "var2":{$first:"$var2"},
          "var3":{$first:"$var3"},
          "field":{$first:"$comments.field"},
          "names":{$push:"$comments.names"}}},
 {$project:{"comments":{"names":"$names","field":"$field"},"var1":1,
            "var2":1,"var3":1}}  
 ],{"allowDiskUse":true})

If your collection is large, you might want to add a $match criteria in the beginning of the aggregation pipeline to filter records or use (allowDiskUse:true), to facilitate sorting large number of records.

 db.collection.aggregate([
 {$match:{"_id":someId}},
 {$unwind:"$comments.names"},
 {$sort:{"comments.names.number":-1}},
 {$group:{"_id":"$_id",
          "var1":{$first:"$var1"},
          "var2":{$first:"$var2"},
          "var3":{$first:"$var3"},
          "field":{$first:"$comments.field"},
          "names":{$push:"$comments.names"}}},
 {$project:{"comments":{"names":"$names","field":"$field"},"var1":1,
            "var2":1,"var3":1}}  
 ])

What The below query does:

db.collection.find().sort( { "comments.names.number": -1 } )

is to find all the documents, then sort those documents based on the number field in descending order. What this actually does is for each document get the comments.names.number field value which is the largest, for each document. And then sort the parent documents based on this number. It doesn't manipulate the names array inside each parent document.

BatScream
  • 19,260
  • 4
  • 52
  • 68
0

You need update document for sort an array.

db.collection.update(
   { _id: 1 },
   {
     $push: {
       comments.names: {
          $each: [ ],
          $sort: { number: -1 }
       }
     }
   }
)

check documentation here: http://docs.mongodb.org/manual/reference/operator/update/sort/#use-sort-with-other-push-modifiers

Juan de Parras
  • 768
  • 4
  • 18
0

MongoDB queries sort the result documents based on the collection of fields specified in the sort. They do not sort arrays within a document. If you want the array sorted, you need to sort it yourself after you retrieve the document, or store the array in sorted order. See this old SO answer from Stennie.

Community
  • 1
  • 1
wdberkeley
  • 11,531
  • 1
  • 28
  • 23