How to sort array inside collection record in MongoDB?

Question

I have a collection of students, each with a record that looks like the following and I want to sort the scores array in descending order of score.

what does that incantation look like on the mongo shell?

> db.students.find({'_id': 1}).pretty()
{
        "_id" : 1,
        "name" : "Aurelia Menendez",
        "scores" : [
                {
                        "type" : "exam",
                        "score" : 60.06045071030959
                },
                {
                        "type" : "quiz",
                        "score" : 52.79790691903873
                },
                {
                        "type" : "homework",
                        "score" : 71.76133439165544
                },
                {
                        "type" : "homework",
                        "score" : 34.85718117893772
                }
        ]
}

I'm trying this incantation....

 doc = db.students.find()

 for (_id,score) in doc.scores:
     print _id,score

but it's not working.

score 64 · Accepted Answer · answered Nov 19 '12 at 13:45

64

You will need to manipulate the embedded array in your application code or using the new Aggregation Framework in MongoDB 2.2.

Example aggregation in the mongo shell:

db.students.aggregate(
    // Initial document match (uses index, if a suitable one is available)
    { $match: {
        _id : 1
    }},

    // Expand the scores array into a stream of documents
    { $unwind: '$scores' },

    // Filter to 'homework' scores 
    { $match: {
        'scores.type': 'homework'
    }},

    // Sort in descending order
    { $sort: {
        'scores.score': -1
    }}
)

Sample output:

{
    "result" : [
        {
            "_id" : 1,
            "name" : "Aurelia Menendez",
            "scores" : {
                "type" : "homework",
                "score" : 71.76133439165544
            }
        },
        {
            "_id" : 1,
            "name" : "Aurelia Menendez",
            "scores" : {
                "type" : "homework",
                "score" : 34.85718117893772
            }
        }
    ],
    "ok" : 1
}

answered Nov 19 '12 at 13:45

Stennie

63,885
14
149
175

3

You can change the end of the aggregation pipeline to sort in ascending order (so the lowest comes first) and limit to 1 document: ` { $sort: { 'scores.score': 1 }}, { $limit : 1 }` – Stennie Nov 19 '12 at 23:44
Issue of duplicate data, you are going to repeat name in every object.So if i have 20 fields in upper level , then should i repeat ? – Prabjot Singh Sep 06 '16 at 08:18
@PrabjotSingh I'm not entirely clear what your question is, but instead of discussing in the comments you should post a new question with an example of your document structure, desired output, and version of MongoDB server/driver. – Stennie Sep 06 '16 at 14:45
1

I agree with @PrabjotSingh scores be returned as an embedded array? Like the question suggests. – F.O.O Jul 12 '19 at 05:30
@F.O.O This question is 6.5 years old and there are different options now depending on your version of MongoDB server. Please post a new question with details relevant to your environment and the problem you are trying to solve. – Stennie Jul 12 '19 at 08:43
However it is not possible to re-wind without disturbing the sorted order. – Firoj Siddiki Aug 20 '21 at 10:18

Xavier Guihot · Answer 2 · 2022-03-15T06:00:12.680

13

Starting in Mongo 5.2, it's the exact use case for the new $sortArray aggregation operator:

// {
//   name: "Aurelia Menendez",
//   scores: [
//     { type: "exam",     score: 60.06 }
//     { type: "quiz",     score: 52.79 }
//     { type: "homework", score: 71.76 }
//     { type: "homework", score: 34.85 }
//   ]
// }
db.collection.aggregate([
  { $set: {
    scores: {
      $sortArray: {
        input: "$scores",
        sortBy: { score: -1 }
      }
    }
  }}
])
// {
//   name: "Aurelia Menendez",
//   scores: [
//     { type: "homework", score: 71.76 },
//     { type: "exam",     score: 60.06 },
//     { type: "quiz",     score: 52.79 },
//     { type: "homework", score: 34.85 }
//   ]
// }

This:

sorts ($sortArray) the scores array (input: "$scores")
by applying a sort on scores (sortBy: { score: -1 })
without having to apply a combination of expensive $unwind, $sort and $group stages

edited Mar 15 '22 at 06:00

answered Dec 26 '21 at 07:09

Xavier Guihot

54,987
21
291
190

What if it's an array only. e.g [1,2,3,4,5] – Ryan Aquino Jan 06 '23 at 13:20
2

@RyanAquino [sort-an-array-of-integers](https://www.mongodb.com/docs/manual/reference/operator/aggregation/sortArray/#sort-an-array-of-integers) – Xavier Guihot Jan 06 '23 at 13:28
1

This one is **the only** correct answer to given question. All other answer are using different input data or update existing data or remove scores rather than sort the array or use a different language despite it asked for mongo shell. – Wernfried Domscheit May 09 '23 at 08:54

score 8 · Answer 3 · answered Jun 28 '17 at 11:04

Since this question can be managed in different ways i want to say that another solution is "insert and sort", in this way you will get the Ordered array at the moment you will made a Find().

Consider this data:

{
   "_id" : 5,
   "quizzes" : [
      { "wk": 1, "score" : 10 },
      { "wk": 2, "score" : 8 },
      { "wk": 3, "score" : 5 },
      { "wk": 4, "score" : 6 }
   ]
}

Here we will update the Document, make the Sort.

db.students.update(
   { _id: 5 },
   {
     $push: {
       quizzes: {
          $each: [ { wk: 5, score: 8 }, { wk: 6, score: 7 }, { wk: 7, score: 6 } ],
          $sort: { score: -1 },
          $slice: 3 // keep the first 3 values
       }
     }
   }
)

Result is:

{
  "_id" : 5,
  "quizzes" : [
     { "wk" : 1, "score" : 10 },
     { "wk" : 2, "score" : 8 },
     { "wk" : 5, "score" : 8 }
  ]
}

Documentation: https://docs.mongodb.com/manual/reference/operator/update/sort/#up._S_sort

This will update existing data, in the question it asks only to **query** the data. — Wernfried Domscheit, May 09 '23 at 08:41

score 5 · Answer 4 · answered Jun 16 '15 at 09:41

5

That's how we could solve this with JS and mongo console:

db.students.find({"scores.type": "homework"}).forEach(
  function(s){
    var sortedScores = s.scores.sort(
      function(a, b){
        return a.score<b.score && a.type=="homework";
      }
    );
    var lowestHomeworkScore = sortedScores[sortedScores.length-1].score;
    db.students.update({_id: s._id},{$pull: {scores: {score: lowestHomeworkScore}}}, {multi: true});
  })

answered Jun 16 '15 at 09:41

Aleksandr Panasyuk

313
2
9

4

Dude? You spoiled the fun. – markphd Aug 21 '15 at 18:49
Does the `{"scores.type": "homework"}` filter expression inside find() achieve something? – Treefish Zhang Jan 25 '17 at 17:29
@TreefishZhang Why shouldn't it? – Aleksandr Panasyuk Nov 16 '17 at 09:51
@AlexanderPanasyuk What did it achieve? -Did it filter out some students? – Treefish Zhang Nov 17 '17 at 02:10
This will update existing data, in the question it asks only to **query** the data. Running a loop (with `forEach`) and update the documents one-by-one will usually let to poor performance. `{multi: true}` does not make sense when you filter on primary key `_id` – Wernfried Domscheit May 09 '23 at 08:42

score 3 · Answer 5 · edited Jan 24 '19 at 14:06

3

In order to sort array, follow these steps:

1) use unwind to iterate through array

2) sort array

3) use group to merge objects of array into one array

4) then project other fields

Query

db.taskDetails.aggregate([
    {$unwind:"$counter_offer"},
    {$match:{_id:ObjectId('5bfbc0f9ac2a73278459efc1')}},
    {$sort:{"counter_offer.Counter_offer_Amount":1}},
   {$unwind:"$counter_offer"},
   {"$group" : {_id:"$_id",
    counter_offer:{ $push: "$counter_offer" },
    "task_name": { "$first": "$task_name"},
    "task_status": { "$first": "$task_status"},
    "task_location": { "$first": "$task_location"},
}}

]).pretty()

edited Jan 24 '19 at 14:06

Serg M Ten

5,568
4
25
48

answered Jan 24 '19 at 13:21

Akshay Dhawle

147
2

Opposed to `$addToSet`, using `$push` preserves the order of the array, sorted in the preceding step. – Alexander Oct 10 '19 at 13:23
Worked as asked. But I don't have unwind again. – Utkarsh Jun 13 '22 at 18:01

score 2 · Answer 6 · answered Apr 05 '14 at 12:02

Here is the java code which can be used to find out the lowest score in the array and remove it.

public class sortArrayInsideDocument{
public static void main(String[] args) throws UnknownHostException {
    MongoClient client = new MongoClient();
    DB db = client.getDB("school");
    DBCollection lines = db.getCollection("students");
    DBCursor cursor = lines.find();
    try {
        while (cursor.hasNext()) {
            DBObject cur = cursor.next();
            BasicDBList dbObjectList = (BasicDBList) cur.get("scores");
            Double lowestScore = new Double(0);
            BasicDBObject dbObject = null;
            for (Object doc : dbObjectList) {
                BasicDBObject basicDBObject = (BasicDBObject) doc;
                if (basicDBObject.get("type").equals("homework")) {
                    Double latestScore = (Double) basicDBObject
                            .get("score");
                    if (lowestScore.compareTo(Double.valueOf(0)) == 0) {
                        lowestScore = latestScore;
                        dbObject = basicDBObject;

                    } else if (lowestScore.compareTo(latestScore) > 0) {
                        lowestScore = latestScore;
                        dbObject = basicDBObject;
                    }
                }
            }
            // remove the lowest score here.
            System.out.println("object to be removed : " + dbObject + ":"
                    + dbObjectList.remove(dbObject));
            // update the collection
            lines.update(new BasicDBObject("_id", cur.get("_id")), cur,
                    true, false);
        }
    } finally {
        cursor.close();
    }
}
}

Nice! Good example...With java 8 we can minimize the compare part. — Pinaki Mukherjee, Jun 13 '15 at 03:51
@Vel How does removing the `dbObject` from `dbObjectList`, remove from the `cur` DBObject? What is the link between `cur` and `dbObjectList`? — user2761431, Aug 20 '15 at 05:41

score 0 · Answer 7 · answered Aug 22 '15 at 21:40

It's easy enough to guess, but anyway, try not cheat with mongo university courses because you won't understand basics then.

db.students.find({}).forEach(function(student){ 

    var minHomeworkScore,  
        scoresObjects = student.scores,
        homeworkArray = scoresObjects.map(
            function(obj){
                return obj.score;
            }
        ); 

    minHomeworkScore = Math.min.apply(Math, homeworkArray);

    scoresObjects.forEach(function(scoreObject){ 
        if(scoreObject.score === minHomeworkScore){ 
            scoresObjects.splice(scoresObjects.indexOf(minHomeworkScore), 1); 
        } 
    });

    printjson(scoresObjects);

});

score 0 · Answer 8 · answered Aug 04 '20 at 16:53

Order Title and Array title also and return whole collection data Collection name is menu

[
            {
                "_id": "5f27c5132160a22f005fd50d",
                "title": "Gift By Category",
                "children": [
                    {
                        "title": "Ethnic Gift Items",
                        "s": "/gift?by=Category&name=Ethnic"
                    },
                    {
                        "title": "Novelty Gift Items",
                        "link": "/gift?by=Category&name=Novelty"
                    }
                ],
                "active": true
            },
            {
                "_id": "5f2752fc2160a22f005fd50b",
                "title": "Gift By Occasion",
                "children": [
                    {
                        "title": "Gifts for Diwali",
                        "link": "/gift-for-diwali" 
                    },
                    {
                        "title": "Gifts for Ganesh Chathurthi",
                        "link": "/gift-for-ganesh-chaturthi",
                    }
                ],
                
                "active": true
            }
    ]

Query as below

let menuList  = await  Menu.aggregate([
                { 
                    $unwind: '$children'
                }, 
                {
                    $sort:{"children.title":1}
                },
                {   
                    $group : { _id : "$_id",
                        root: { $mergeObjects: '$$ROOT' },   
                        children: { $push: "$children" } 
                    } 
                },
                {
                    $replaceRoot: {
                        newRoot: {
                            $mergeObjects: ['$root', '$$ROOT']
                        }
                    }
                },
                {
                    $project: {
                        root: 0 
                    }
                },
                { 
                    $match: {
                                $and:[{'active':true}],
                            }
                },
                {
                    $sort:{"title":1}
                }                  
    ]);

score 0 · Answer 9 · answered May 09 '23 at 09:05

Before question was edited it was

I want to sort the 'type': 'homework' scores in descending order.

This means, the array has to be filtered and sorted. In more recent version of MongoDB you can do it like this:

db.collection.aggregate([
   {
      $set: {
         scores: {
            $sortArray: {
               input: {
                  $filter: {
                     input: "$scores",
                     cond: { $eq: ["$$this.type", "homework"] }
                  }
               },
               sortBy: { score: -1 }
            }
         }
      }
   }
])

score -1 · Answer 10 · edited Apr 10 '16 at 19:40

I believe you are doing M101P: MongoDB for Developers where homework 3.1 is to remove the lower one from two homework scores. Since aggregations were not taught up to that point you can do something like this:

import pymongo

conn = pymongo.MongoClient('mongodb://localhost:27017')
db = conn.school
students = db.students

for student_data in students.find():
    smaller_homework_score_seq = None
    smaller_homework_score_val = None
    for score_seq, score_data in enumerate(student_data['scores']):
        if score_data['type'] == 'homework':
            if smaller_homework_score_seq is None or smaller_homework_score_val > score_data['score']:
                smaller_homework_score_seq = score_seq
                smaller_homework_score_val = score_data['score']
    students.update({'_id': student_data['_id']}, {'$pop': {'scores': smaller_homework_score_seq}})

OP was for the mongo js shell, but this is a super clean Python example! — charles ross, Aug 27 '18 at 13:14

Treefish Zhang · Answer 11 · 2017-01-25T19:12:03.770

This is my approach using pyMongo, the Python driver to MongoDB:

import pymongo


conn = pymongo.MongoClient('mongodb://localhost')

def remove_lowest_hw():
    db = conn.school
    students = db.students

    # first sort scores in ascending order
    students.update_many({}, {'$push':{'scores':{'$each':[], '$sort':{'score': 1}}}})

    # then collect the lowest homework score for each student via projection
    cursor = students.find({}, {'scores':{'$elemMatch':{'type':'homework'}}})

    # iterate over each student, trimming each of the lowest homework score
    for stu in cursor:
        students.update({'_id':stu['_id']}, {'$pull':{'scores':{'score':stu['scores'][0]['score']}}})

remove_lowest_hw()

conn.close()

score -2 · Answer 12 · edited Apr 30 '21 at 16:49

the answer of @Stennie is fine, maybe a $group operator would be useful to keep the original document, without exploding it in many documents (one by score).

I just add another solution when using javascript for your application.

if you query only one document, it's sometimes easier to sort the embedded array by JS, instead of doing an aggregate. When your document has a lot of fields, it's even better than using $push operator, otherwise you've to push all the fields one by one, or use $$ROOT operator (am I wrong ?)

My example code uses Mongoose.js : Suppose you have initialized you Students model.

// Sorting
function compare(a, b) {
  return a.score - b.score;
}

Students.findById('1', function(err, foundDocument){
  foundDocument.scores = foundDocument.scores.sort(compare);
  
  // do what you want here...
  // foundModel keeps all its fields
});

score -2 · Answer 13 · answered Nov 01 '15 at 19:09

this work for me, it is a little rough code but the results of the lowest tasks for each student are correct.

var scores_homework = []
db.students.find({"scores.type": "homework"}).forEach(
  function(s){
    s.scores.forEach(
        function(ss){
            if(ss.type=="homework"){
                ss.student_id = s._id
                scores_homework.push(ss)
            }
        }
    )
})
for(i = 0; i < scores_homework.length; i++)
{
    var b = i+1;
    var ss1 = scores_homework[i];
    var ss2 = scores_homework[b];
    var lowest_score = {};
    if(ss1.score > ss2.score){
        lowest_score.type = ss2.type;
        lowest_score.score = ss2.score;
        db.students.update({_id: ss2.student_id},{$pull: {scores: {score: lowest_score.score}}});
    }else if(ss1.score < ss2.score){
        lowest_score.type = ss1.type;
        lowest_score.score = ss1.score;
        db.students.update({_id: ss1.student_id},{$pull: {scores: {score: lowest_score.score}}});
    }else{
        lowest_score.type = ss1.type;
        lowest_score.score = ss1.score;
        db.students.update({_id: ss1.student_id},{$pull: {scores: {score: lowest_score.score}}});
    }
    i++
}

score -2 · Answer 14 · answered Jan 29 '17 at 07:25

This is how I have implemented in Java (Have kept it simple so that it's easier to understand) -

Approach :

Get scores array from student collection
Get all score values from scores array where type == homework
Sort the score values so that lowest becomes 1st element [score.get(0)]
Then, loop through the main scores and create new copy of scores array while skipping elements where type == homework && score == scores.get(0)
Finally, update the new scores array to student document.

Below is working Java code:

    public void removeLowestScore(){
    //Create mongo client and database connection and get collection
    MongoClient client = new MongoClient("localhost");
    MongoDatabase database = client.getDatabase("school");
    MongoCollection<Document> collection = database.getCollection("students");


    FindIterable<Document> docs = collection.find();
    for (Document document : docs) {

        //Get scores array
        ArrayList<Document> scores = document.get("scores", ArrayList.class);           

        //Create a list of scores where type = homework
        List<Double> homeworkScores = new ArrayList<Double>();
        for (Document score : scores) {
            if(score.getString("type").equalsIgnoreCase("homework")){
                homeworkScores.add(score.getDouble("score"));   
            }
        }

        //sort homework scores
        Collections.sort(homeworkScores);

        //Create a new list to update into student collection
        List<Document> newScoresArray = new ArrayList<Document>();
        Document scoreDoc = null;

        //Below loop populates new score array with eliminating lowest score of "type" = "homework"
        for (Document score : scores) {
            if(score.getString("type").equalsIgnoreCase("homework") && homeworkScores.get(0) == score.getDouble("score")){                  
                    continue;                       
                }else{
                    scoreDoc = new Document("type",score.getString("type"));
                    scoreDoc.append("score",score.getDouble("score"));
                    newScoresArray.add(scoreDoc);
                }               
            }           

        //Update the scores array for every student using student _id
        collection.updateOne(Filters.eq("_id", document.getInteger("_id")), new Document("$set",new Document("scores",newScoresArray)));
    }       
}

score -2 · Answer 15 · answered Jan 30 '17 at 16:25

Certainly it's late, but I just want to contribute my own solution on Mongo Shell:

var students = db.getCollection('students').find({});
for(i = 0 ; i < students.length(); i++) {
    var scores = students[i].scores;
    var tmp = [];
    var min = -1 ;
    var valueTmp = {};
    for(j = 0 ; j < scores.length; j++) {        
        if(scores[j].type != 'homework') {
            tmp.push(scores[j]);
        } else {
            if (min == -1) {
                min = scores[j].score;
                valueTmp = scores[j];
            } else {
                if (min > scores[j].score) {
                    min = scores[j].score;
                    tmp.push(valueTmp);
                    valueTmp = scores[j];
                } else {
                    tmp.push(scores[j]);
                }
            }
        }
    }
    db.students.updateOne({_id:students[i]._id},
                            {$set:{scores:tmp}});
}

score -3 · Answer 16 · edited Aug 21 '16 at 10:57

-3

sort by the score can be simple like:

db.students.find({_id:137}).sort({score:-1}).pretty()

but you need to find the one for type:homework ...

edited Aug 21 '16 at 10:57

Prasad Khode

6,602
11
44
59

answered Apr 03 '15 at 17:44

newbie

13
2
3

$sort used in conjunction with $push and $each will do at the mongo shell: `db.students.updateMany({}, {'$push':{'scores':{'$each':[], '$sort':{'score': 1}}}})` per [mongodb documentation](https://docs.mongodb.com/manual/reference/operator/update/sort/) – Treefish Zhang Jan 25 '17 at 17:16

score -5 · Answer 17 · answered Nov 19 '12 at 08:17

-5

it should be something like this:

db.students.find().sort(scores: ({"score":-1}));

answered Nov 19 '12 at 08:17

slownage

154
1
3
11

If this is written in mongoshell this isn't valid, neither will it do the job he asks for. Correctly it should be db.students.find().sort({"scores.score":-1}) but this doesn't sorts anything (at least nothing I can see), especially not the scores array within the student. As far as I know, you need to manually iterate over those array entries and do the sorting, mongo won't do it. – philnate Nov 19 '12 at 08:28
also philnate is correct....this does not bring the desired result in mongo shell...thanks for the attempt. – thefonso Nov 19 '12 at 09:28
I'm a python and mongodb noob...this link leads to an answer in PHP...I'm looking for a solution in python or in mongo shell. – thefonso Nov 19 '12 at 09:43

How to sort array inside collection record in MongoDB?

17 Answers17

Linked

Related