javascript - How to sort array inside collection record in MongoDB? - Stack Overflow

admin管理员组

文章数量:1136312

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.

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.

• javascript
• arrays
• mongodb
• sorting

Share Improve this question Follow edited Dec 26, 2021 at 7:06 Xavier Guihot 61.6k2525 gold badges317317 silver badges199199 bronze badges asked Nov 19, 2012 at 8:13 thefonsothefonso 3,39066 gold badges4040 silver badges5757 bronze badges

Add a comment  | 

16 Answers 16

Sorted by: Reset to default 69

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
}

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

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

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});
  })

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()

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();
    }
}
}

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);

});

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}
                }                  
    ]);

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 }
            }
         }
      }
   }
])

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

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()

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

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

Approach :

1. Get scores array from student collection
2. Get all score values from scores array where type == homework
3. Sort the score values so that lowest becomes 1st element [score.get(0)]
4. Then, loop through the main scores and create new copy of scores array while skipping elements where type == homework && score == scores.get(0)
5. 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)));
    }       
}

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
});

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 ...

it should be something like this:

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

本文标签： javascriptHow to sort array inside collection record in MongoDBStack Overflow