我有以下 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"
}
}
])
但我得到的結果是：（我只在串列中顯示 1 項）
[
{
"_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
uj5u.com熱心網友回復：
- 用于
$size計算 的submission_docs陣列長度total_submissions。 submission_docs在via中過濾狀態為“AC”的提交，$filter然后計算陣列長度。
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 Playground
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/450654.html
上一篇：MongoDB聚合創建日期到時間戳并在之后與時間戳欄位匹配
下一篇：MongoDB聚合多重操作