how to sort array inside collection record in mong

问题:

MongoDB noob here...

Ok, I have a collection of students, each with a record that looks like the following.... I want to sort the \'type\' : \'homework\' scores in descending order.

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.

回答1:

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
}

回答2:

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

回答3:

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

回答4:

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

});

回答5:

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

回答6:

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

回答7:

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

回答8:

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

回答9:

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

回答10:

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

回答11:

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

回答12:

it should be something like this:

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

回答13:

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