使用运算符获取提交总数和提交数"AC"$lookup

Question

我有以下MongoDB集合：

db={
"problems": [
{
"problemId": 1,
"title": "dummy 1",
},
{
"problemId": 2,
"title": "dummy 2",
}
],
"submissions": [
{
"submissionId": 1,
"status": "AC",
"problemId": 1,
},
{
"submissionId": 2,
"status": "AC",
"problemId": 1,
},
{
"submissionId": 3,
"status": "WA",
"problemId": 2,
},
{
"submissionId": 4,
"status": "WA",
"problemId": 1,
},
{
"_id": 5,
"status": "AC",
"problemId": 2,
},
{
"_id": 6,
"status": "WA",
"problemId": 2,
}
]
}

我想显示提交总数和状态为"AC"的解决方案的数量。我希望结果看起来像这样：

[
{
"problemId": 1,
"title": "dummy 1",
"total_submissions": 3,
"accepted_submissions": 2
},
]

到目前为止，我已经使用了$lookup运算符，并做了如下操作：

db.problems.aggregate([
{
"$lookup": {
"from": "submissions",
"localField": "problemId",
"foreignField": "problemId",
"as": "submission_docs"
}
}
])

但我得到的结果是：(我只是在列表中显示一个项目(

[
{
"_id": ObjectId("5a934e000102030405000000"),
"problemId": 1,
"submission_docs": [
{
"_id": ObjectId("5a934e000102030405000003"),
"problemId": 1,
"status": "AC",
"submissionId": 1
},
{
"_id": ObjectId("5a934e000102030405000004"),
"problemId": 1,
"status": "AC",
"submissionId": 2
},
{
"_id": ObjectId("5a934e000102030405000006"),
"problemId": 1,
"status": "WA",
"submissionId": 4
}
],
"title": "dummy 1"
}
]

MongoDB游乐场可以在这里找到：https://mongoplayground.net/p/YRdpyQN5f00

Answer 1

1. 使用$size计算total_submissions的submission_docs数组长度
2. 过滤提交的状态"；AC"；在CCD_ 4中经由CCD_

db.problems.aggregate([
{
"$lookup": {
"from": "submissions",
"localField": "problemId",
"foreignField": "problemId",
"as": "submission_docs"
}
},
{
$project: {
_id: 0,
problemId: 1,
title: 1,
total_submissions: {
$size: "$submission_docs"
},
accepted_submissions: {
$size: {
$filter: {
input: "$submission_docs",
cond: {
$eq: [
"$$this.status",
"AC"
]
}
}
}
}
}
}
])

Mongo游乐场示例