聚合组多个字段并计算平均mondodb

Question

我的样本数据如下

db.student.insert({"name":"Vikash", "sub":"Physics", "marks":10})
db.student.insert({"name":"Vikash", "sub":"Math", "marks":20})
db.student.insert({"name":"Raj", "sub":"Physics", "marks":5})
db.student.insert({"name":"Raj", "sub":"Math", "marks":20})
db.student.insert({"name":"Vikash", "sub":"Physics", "marks":20})
db.student.insert({"name":"Vikash", "sub":"Math", "marks":30})
db.student.insert({"name":"Raj", "sub":"Physics", "marks":40})
db.student.insert({"name":"Raj", "sub":"Math", "marks":10})

样本输出为:

{
_id:"Vikash":[{
"sub":"Physics",
"avgMarks":15
},
{
"sub":"Math",
"avgMarks":25
}]
}
{
_id:"Raj":[{
"sub":"Physics",
"avgMarks":22.5
},
{
"sub":"Math",
"avgMarks":15
}]
}

Answer 1

• $group由name和sub得到平均marks由$avg得到
• $group由唯一的name构建主题数组
• $arrayToObject将对象的键值数组转换为对象
• $replaceRoot将上述对象替换为根

db.collection.aggregate([
{
$group: {
_id: {
name: "$name",
sub: "$sub"
},
avgMarks: { $avg: "$marks" }
}
},
{
$group: {
_id: "$_id.name",
sub: {
$push: {
sub: "$_id.sub",
avgMarks: "$avgMarks"
}
}
}
},
{
$replaceRoot: {
newRoot: {
$arrayToObject: [
[{ k: "$_id", v: "$sub" }]
]
}
}
}
])

游乐场