需要求和的分组的投影结果

Question

我正试图让我的头围绕一个特定的问题，以决定是否采取一些个人项目转换为MongoDb在上周完成基础课程后的冒险。我想要实现的是基于分组的数据表示，然后最终选择该组的特定部分来创建一个新的投影，显示我的最终结果。在目前的代码中，我们进行分组，然后进行子选择以创建最终数据集，我希望这可以在一次命中完成。

示例文档

{
"_id": {
"$oid": "600d88b0d7016d5675cd59bd"
},
"DeviceId": {
"$oid": "600d729764ea780882ac559b"
},
"UserId": {
"$oid": "600b660eff59aab915985b1d"
},
"Date": {
"$date": {
"$numberLong": "1611499696095"
}
},

"Records": [
{
"Count": {
"$numberInt": "10"
},
"Test1": {
"Inconclusive": null,
"Passed": true,
"Failed": null
},
"Test2": {
"Inconclusive": null,
"Passed": true,
"Failed": null
}

},
{
"Count": {
"$numberInt": "15"
},
"Test1": {
"Inconclusive": true,
"Passed": null,
"Failed": null
},
"Test2": {
"Inconclusive": null,
"Passed": true,
"Failed": null
}

},
{
"Count": {
"$numberInt": "15"
},
"Test1": {
"Inconclusive": true,
"Passed": null,
"Failed": null
},
"Test2": {
"Inconclusive": null,
"Passed": null,
"Failed": true
}

}
]
}

最终，我想要得到的是尽可能接近这个;

{
"DeviceId": "600d729764ea780882ac559b",
"Test1Inconclusive": 30,
"Test1Passed": 10,
"Test1Failed": 0,
"Test2Inconclusive": 0,
"Test2Passed": 25,
"Test2Failed": 15
}

到目前为止，我所能得到的只是分组数据，而在现有代码(实体框架/SQL服务器)中，我将使用Linq来提取SUM'd值。

[{
$match: {
UserId: ObjectId('600b660eff59aab915985b1d')
}
}, {
$unwind: {
path: '$Records'
}
}, {
$group: {
_id: {
DeviceId: '$DeviceId',
Test1Inconclusive: '$Records.Test1.Inconclusive',
Test1Passed: '$Records.Test1.Passed',
Test1Failed: '$Records.Test1.Failed',
Test2Inconclusive: '$Records.Test2.Inconclusive',
Test2Passed: '$Records.Test2.Passed',
Test2Failed: '$Records.Test2.Failed',
},
Count: {
$sum: '$Records.Count'
}
}
}, {}]

我不确定是否有可能做我想要的，如果是这样，如何做下一个投影步骤，同时执行这个分组数据的子选择。甚至可能我的方法从一开始就有缺陷，所以你可以随意改变它。

奖金互联网点，如果你也可以给我MongoDb c#语法做同样的(在一个MongoCollection)

Answer 1

从@turivishal的初始版本开始，下面的答案起作用了;

db.collection.aggregate([
{
$match: {
UserId: ObjectId("600b660eff59aab915985b1d")
}
},
{
$unwind: {
path: "$Records"
}
},
{
$group: {
_id: "$DeviceId",
Test1Inconclusive: {
$sum: {
$cond: [
{
$eq: [
"$Records.Test1.Inconclusive",
true
]
},
"$Records.Count",
0
]
}
},
Test1Passed: {
$sum: {
$cond: [
{
$eq: [
"$Records.Test1.Passed",
true
]
},
"$Records.Count",
0
]
}
},
Test1Failed: {
$sum: {
$cond: [
{
$eq: [
"$Records.Test1.Failed",
true
]
},
"$Records.Count",
0
]
}
},
Test2Inconclusive: {
$sum: {
$cond: [
{
$eq: [
"$Records.Test2.Inconclusive",
true
]
},
"$Records.Count",
0
]
}
},
Test2Passed: {
$sum: {
$cond: [
{
$eq: [
"$Records.Test2.Passed",
true
]
},
"$Records.Count",
0
]
}
},
Test2Failed: {
$sum: {
$cond: [
{
$eq: [
"$Records.Test2.Failed",
true
]
},
"$Records.Count",
0
]
}
},
Count: {
$sum: "$Records.Count"
}
}
}
])