node.js - MongoDB 聚合嵌套数据

Question

我有如下嵌套数据，

{
"_id" : ObjectId("5a30ee450889c5f0ebc21116"),
"academicyear" : "2017-18",
"fid" : "be02",
"fname" : "ABC",
"fdept" : "Comp",
"degree" : "BE",
"class" : "1",
"sem" : "8",
"dept" : "Comp",
"section" : "Theory",
"subname" : "BDA",
"fbValueList" : [ 
    {
        "_id" : ObjectId("5a30eecd3e3457056c93f7af"),
        "score" : 20,
        "rating" : "Fair"
    }, 
    {
        "_id" : ObjectId("5a30eefd3e3457056c93f7b0"),
        "score" : 10,
        "rating" : "Fair"
    }, 
    {
        "_id" : ObjectId("5a337e53341bf419040865c4"),
        "score" : 88,
        "rating" : "Excellent"
    }, 
    {
        "_id" : ObjectId("5a337ee2341bf419040865c7"),
        "score" : 75,
        "rating" : "Very Good"
    }, 
    {
        "_id" : ObjectId("5a3380b583dde50ddcea350e"),
        "score" : 72,
        "rating" : "Very Good"
    }
]
},
{
    "_id" : ObjectId("5a3764f1bc19b77dd9fd9a57"),
    "academicyear" : "2017-18",
    "fid" : "be02",
    "fname" : "ABC",
    "fdept" : "Comp",
    "degree" : "BE",
    "class" : "1",
    "sem" : "5",
    "dept" : "Comp",
    "section" : "Theory",
    "subname" : "BDA",
    "fbValueList" : [ 
        {
            "_id" : ObjectId("5a3764f1bc19b77dd9fd9a59"),
            "score" : 88,
            "rating" : "Excellent"
        }, 
        {
            "_id" : ObjectId("5a37667aee64bce1b14747d2"),
            "score" : 74,
            "rating" : "Good"
        }, 
        {
            "_id" : ObjectId("5a3766b3ee64bce1b14747dc"),
            "score" : 74,
            "rating" : "Good"
        }
    ]
}

我们正在尝试使用它来执行聚合，

db.fbresults.aggregate([{$match:{academicyear:"2017-18",fdept:'Comp'}},{$group:{_id: {fname: "$fname", rating:"$fbValueList.rating"},count: {"$sum":1}}}])

我们得到的结果是这样的，

{ "_id" : { "fname" : "ABC", "rating" : [ "Fair","Fair","Excellent","Very Good", "Very Good",  "Excellent", "Good", "Good" ] }, "count" : 2 }

但我们期望的结果是这样的，

{ "_id" : { "fname" : "ABC", "rating_group" : [ 
        { 
            rating: "Excellent"
            count: 2 
        },
            { 
            rating: "Very Good"
            count: 2 
        },
            { 
            rating: "Good"
            count: 2 
        },
            { 
            rating: "Fair"
            count: 2 
        },

    ] }, "count" : 2 }

我们希望通过姓名获取各个教师小组的信息，并通过他们的评分响应和评分计数获取该小组内部的信息。

我们已经尝试过这个，但没有结果。 Mongodb Aggregate Nested Group

Answer 1

这应该能让你继续:

db.collection.aggregate([{
    $match: {
        academicyear: "2017-18",
        fdept:'Comp'
    }
}, {
    $unwind: "$fbValueList" // flatten the fbValueList array into multiple documents
}, {
    $group: {
        _id: {
            fname: "$fname",
            rating:"$fbValueList.rating"
        },
        count: {
            "$sum": 1 // this will give us the count per combination of fname and fbValueList.rating
        }
    }
}, {
    $group: {
        _id: "$_id.fname", // we only want one bucket per fname
        rating_group: {
            $push: { // we push the exact structure you were asking for
                rating: "$_id.rating",
                count: "$count"
            }
        },
        count: {
            $avg: "$count" // this will be the average across all entries in the fname bucket
        }
    }
}])