MongoDB按数组元素分组的数组中匹配字符串的个数

Question

我有一个MongoDB集合存储调查的答案。答案通常是单选按钮，上面有"优秀"、"不错"或"差"之类的回答。我试图生成一个查询，为每个问题返回给定响应的总数。响应当前存储在字符串数组中。数组中的第0个位置是问题1的答案，以此类推。

我目前有一个聚合查询，它以以下TRUNCATED格式返回数据:

[{ 
"name" : "Medical Visit Survey", 
"question" : [
"Ease of making an appointment?", 
"About how long was your wait time before being seen by the provider?", 
"Professionalism of person who took your call?"
], 
"type" : [ "radiobutton", "radiobutton", "radiobutton" ], 
"answers" : [ "Excellent",  "Less than 20 minutes", "Excellent" ]
},
{ 
"name" : "Medical Visit Survey", 
"question" : [
"Ease of making an appointment?", 
"About how long was your wait time before being seen by the provider?", 
"Professionalism of person who took your call?"
], 
"type" : [ "radiobutton",  "radiobutton", "radiobutton" ], 
"answers" : ["Excellent",  "Less than 20 minutes",  "Very Good" ]
}]

产生如下输出的最佳方法是什么?

[{
"name" : "Medical Visit Survey",
"question" : "Ease of making an appointment?",
"type" : "radiobutton",
"answers": { 
"Excellent": 2,
"Good": 3,
"Poor": 1
}

},
{
"name" : "Medical Visit Survey",
"question" : "About how long was your wait time before being seen by the provider?",
"type" : "radiobutton",
"answers": { 
"Less than 20 minutes": 2,
"More than 20 minutes": 3,
"More than 60 minutes": 1
}

}
]

我尝试过类似以下的查询:

[
{$unwind: "$answers" },
{ $group: { _id: "$answers", count: { $sum: 1 } } }
]

输出根据给出的答案计算响应，但不考虑问题编号(元素在数组中的位置)。

我有一个可能有用的mongoplayground链接:https://mongoplayground.net/p/4_uM7khrMEM

如有任何帮助，不胜感激。

Answer 1

我不确定是否有最好的方法来做到这一点，但我建议一个聚合查询，

• $unwind解构question数组，并在问题的每个元素中包含index字段中的数组索引
• $arrayElemAt选择index字段的特定answer,type字段也一样
• $groupbyquestionandanswer，选择必填项并计数总数
• $group仅question和构建answers数组在键值格式
• $arrayToObject转换answers数组为对象

[
{
$unwind: {
path: "$question",
includeArrayIndex: "index"
}
},
{
$group: {
_id: {
question: "$question",
answer: { $arrayElemAt: ["$answers", "$index"] }
},
name: { $first: "$name" },
type: { $first: { $arrayElemAt: ["$type", "$index"] } },
count: { $sum: 1 }
}
},
{
$group: {
_id: "$_id.question",
answers: {
$push: { k: "$_id.answer", v: "$count" }
},
name: { $first: "$name" },
type: { $first: "$type" }
}
},
{ $addFields: { answers: { $arrayToObject: "$answers" } } }
]

游乐场