javascript - Mongoose :聚合和计数

Question

我有一个 NodeJS/MongoDB 应用程序，它的数据存储在一个名为 "Feedback" 的集合中。数据如下所示:

[
    {
        "__v": 0,
        "_id": "57d6b2d09f46ca14440ac14e",
        "customerFeedback": [
            {
                "_id": "57d6b2d09f46ca14440ac14f",
                "answer": [
                    {
                        "_id": "57d6b2d09f46ca14440ac150",
                        "answerValue": "cat",
                        "answerWeight": 0
                    }
                ],
                "question": "What is your favourite thing about this shop?",
                "questionId": "57d65edc0132461120fa0afd"
            },
            {
                "_id": "57d6b2d09f46ca14440ac151",
                "answer": [
                    {
                        "_id": "57d6b2d09f46ca14440ac152",
                        "answerValue": "Okay",
                        "answerWeight": 0
                    }
                ],
                "question": "How was your experience today?",
                "questionId": "57d69ef6dbb25611e46e6bc9"
            }
        ],
        "shopId": "SH0001",
        "feedbackCreatedOn": "2016-09-12T13:51:12.703Z",
        "questionsForDay": "2016-09-12T00:00:00Z"
    },
    {
        "__v": 0,
        "_id": "57d6b3389f46ca14440ac157",
        "customerFeedback": [
            {
                "_id": "57d6b3389f46ca14440ac158",
                "answer": [
                    {
                        "_id": "57d6b3389f46ca14440ac159",
                        "answerValue": "cat",
                        "answerWeight": 0
                    }
                ],
                "question": "What is your favourite thing about this shop?",
                "questionId": "57d65edc0132461120fa0afd"
            },
            {
                "_id": "57d6b3389f46ca14440ac15a",
                "answer": [
                    {
                        "_id": "57d6b3389f46ca14440ac15b",
                        "answerValue": "Very Good",
                        "answerWeight": 0
                    }
                ],
                "question": "How was your experience today?",
                "questionId": "57d69ef6dbb25611e46e6bc9"
            },
            {
                "_id": "57d6b3389f46ca14440ac15c",
                "answer": [
                    {
                        "_id": "57d6b3389f46ca14440ac15d",
                        "answerValue": "Cost",
                        "answerWeight": 0
                    }
                ],
                "question": "What would you like us to improve on?",
                "questionId": "57d6b32d9f46ca14440ac153"
            }
        ],
        "shopId": "SH0001",
        "feedbackCreatedOn": "2016-09-12T13:52:56.939Z",
        "questionsForDay": "2016-09-12T00:00:00Z"
    },
    {
        "__v": 0,
        "_id": "57d6c8eb97157f10a4e5c2e7",
        "customerFeedback": [
            {
                "_id": "57d6c8eb97157f10a4e5c2e8",
                "answer": [
                    {
                        "_id": "57d6c8eb97157f10a4e5c2ea",
                        "answerValue": "Customer Experience",
                        "answerWeight": 0
                    },
                    {
                        "_id": "57d6c8eb97157f10a4e5c2e9",
                        "answerValue": "Others",
                        "answerWeight": 0
                    }
                ],
                "question": "What would you like us to improve on?",
                "questionId": "57d6b7d99ee61e47f01e5334"
            }
        ],
        "shopId": "SH0003",
        "feedbackCreatedOn": "2016-09-12T15:25:31.724Z",
        "questionsForDay": "2016-09-12T00:00:00Z"
    }
]

结果数组中有很多这样的条目，但上面的数据可以说明这一点。

我的问题是，对于给定的 shopId 和 questionId，我想计算每个 answerValue 出现的次数。我该怎么做？

我能够使用 find 方法将结果过滤到所需的数据集(例如)

db.Feedback.find({shopId:"SH0001",'customerFeedback.questionId':"57d65edc0132461120fa0afd"})

但我不确定如何将数据聚合成我想要的格式。

Answer 1

这个管道应该给你想要的结果

db.getCollection("yourCollection").aggregate([
    { $unwind: "$customerFeedback" },
    { $unwind: "$customerFeedback.answer" },
    { 
        $group: {
            _id: {
                shopId: "$shopId",
                questionId: "$customerFeedback.questionId",
                answerValue: "$customerFeedback.answer.answerValue"
            },
            count: { $sum: 1 }
        }
    }
])

您的样本数据给出以下输出

/* 1 */
{
    "_id" : {
        "shopId" : "SH0003",
        "questionId" : "57d6b7d99ee61e47f01e5334",
        "answerValue" : "Others"
    },
    "count" : 1.0
}

/* 2 */
{
    "_id" : {
        "shopId" : "SH0003",
        "questionId" : "57d6b7d99ee61e47f01e5334",
        "answerValue" : "Customer Experience"
    },
    "count" : 1.0
}

/* 3 */
{
    "_id" : {
        "shopId" : "SH0001",
        "questionId" : "57d65edc0132461120fa0afd",
        "answerValue" : "cat"
    },
    "count" : 2.0
}

/* 4 */
{
    "_id" : {
        "shopId" : "SH0001",
        "questionId" : "57d69ef6dbb25611e46e6bc9",
        "answerValue" : "Okay"
    },
    "count" : 1.0
}

/* 5 */
{
    "_id" : {
        "shopId" : "SH0001",
        "questionId" : "57d6b32d9f46ca14440ac153",
        "answerValue" : "Cost"
    },
    "count" : 1.0
}

/* 6 */
{
    "_id" : {
        "shopId" : "SH0001",
        "questionId" : "57d69ef6dbb25611e46e6bc9",
        "answerValue" : "Very Good"
    },
    "count" : 1.0
}

当然，您可以使用额外的 $match 过滤结果如果您只对特定值感兴趣，请在这个阶段

---

因评论而更新:

要使用 $match 过滤结果，您可以像这样展开嵌套的客户反馈数组后使用它

...
{ $unwind: "$customerFeedback" },
{ $match: { shopId: "SH0001", "customerFeedback.questionId": "57d65edc0132461120fa0afd" } },
{ $unwind: "$customerFeedback.answer" },
{ 
    $group: {
        _id: {
            shopId: "$shopId",
            questionId: "$customerFeedback.questionId",
            answerValue: "$customerFeedback.answer.answerValue"
        },
        count: { $sum: 1 }
    }
}
...  

这会导致

{
    "_id" : {
        "shopId" : "SH0001",
        "questionId" : "57d65edc0132461120fa0afd",
        "answerValue" : "cat"
    },
    "count" : 2.0
}

如果您在 shopId 和/或 customerFeedback.questionId 上有大量文档或索引，那么您可能需要复制 $match 阶段到你的管道的前面，这样你就可以只展开相应商店的文档，并且至少有一个所需问题的反馈。所以有了这个(从正确性的 Angular 来看可选优化)它看起来像这样

...
{ $match: { shopId: "SH0001", "customerFeedback.questionId": "57d65edc0132461120fa0afd" } },
{ $unwind: "$customerFeedback" },
{ $match: { shopId: "SH0001", "customerFeedback.questionId": "57d65edc0132461120fa0afd" } },
{ $unwind: "$customerFeedback.answer" },
{ 
    $group: {
        _id: {
            shopId: "$shopId",
            questionId: "$customerFeedback.questionId",
            answerValue: "$customerFeedback.answer.answerValue"
        },
        count: { $sum: 1 }
    }
}
...