如何基于数组中的值对集合进行排序 [英] How do I sort a collection based on values in an array

问题描述

我有一个名为result的集合，具有以下值:-

I have collection named result with following values:-

> db.result.findOne()
{
        "_id" : ObjectId("53b05264421aa97e980ba404"),
        "result" : [
                {
                        "attempted" : 49,
                        "subject_total_marks" : 50,
                        "score" : 15,
                        "correct_subject_answer" : 15,
                        "subject" : "Biology"
                },
                {
                        "attempted" : 30,
                        "subject_total_marks" : 30,
                        "score" : 4,
                        "correct_subject_answer" : 4,
                        "subject" : "Chemistry"
                },
                {
                        "attempted" : 20,
                        "subject_total_marks" : 20,
                        "score" : 7,
                        "correct_subject_answer" : 7,
                        "subject" : "Physics"
                },
                {
                        "attempted" : 99,
                        "correct_subject_answer" : 26,
                        "score" : 26,
                        "subject_total_marks" : 100,
                        "subject" : "Total"
                }
        ],
        "useruid" : NumberLong(548),
        "exam_code" : 301,
        "ess_time" : 1404062120
}

现在，我要基于结果数组中总计"的得分"对集合进行排序.然后根据物理，数学的分数进行分类，然后根据化学和生物学进行分类.

Now I want to sort the collection based on "score" of "total" which is inside result array. Then sort based on score of physics, mathematics and then based on chemistry and finally biology.

推荐答案

您可能已经尝试过，不能通过简单的查找将数组中的特定项目指定为排序"的键".为此，您将需要使用aggregate方法来获取要排序的键.

As you may have already tried, you cannot specify a specific item inside an array as a "key" to "sort" with a simple find. For this you are going to need the aggregate method in order to get the keys you want to sort on.

db.exam.aggregate([

     # Unwind to de-normalize
     { "$unwind": "$result" },

     # Group back to the document and extract each score
     { "$group": {
         "_id": "$_id",
         "result": { "$push": "$result" },
         "useruid": { "$first": "$useruid" },
         "exam_code": { "$first": "$exam_code" },
         "ess_time": { "$first": "$ess_time" },
         "Total": { 
             "$max": {
                 "$cond": [
                     { "$eq": [ "$result.subject", "Total" ] },
                     "$result.score",
                     0
                 ]
             }
         },
         "Physics": { 
             "$max": {
                 "$cond": [
                     { "$eq": [ "$result.subject", "Physics" ] },
                     "$result.score",
                     0
                 ]
             }
         },
         "Mathematics": { 
             "$max": {
                 "$cond": [
                     { "$eq": [ "$result.subject", "Mathematics" ] },
                     "$result.score",
                     0
                 ]
             }
         },
         "Chemistry": { 
             "$max": {
                 "$cond": [
                     { "$eq": [ "$result.subject", "Chemistry" ] },
                     "$result.score",
                     0
                 ]
             }
         },
         "Biology": { 
             "$max": {
                 "$cond": [
                     { "$eq": [ "$result.subject", "Biology" ] },
                     "$result.score",
                     0
                 ]
             }
         }
     }},

     # Sort on those scores
     { "$sort": {
         "Total": -1,
         "Physics": -1,
         "Mathematics": -1,
         "Chemistry": -1,
         "Biology": -1
     }},

     # Project final wanted fields
     { "$project": {
         "result": 1,
         "useruid": 1,
         "exam_code": 1,
         "ess_time": 1
     }}
])

因此，您在这里使用 < 展开数组后的语句.未规范化的文档并没有全部具有相同的值，因为它们现在代表数组中的项目，因此您可以对其进行测试.

So here you "extract" the matching values using the $cond operator within a $max statement after unwinding the array. The de-normalized documents do not all have the same values as they now represent the items in the array, so you test them.

使用这些提取的关键字，您可以再次对整个文档进行排序，然后在不再需要这些字段时将其丢弃.

With those extracted keys you can sort your whole documents again, and then finally discard those fields as you no longer need them.

这篇关于如何基于数组中的值对集合进行排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！