Mongoose中group by结合lookup关联查询并统计嵌套答案响应数如何实现

你的现有查询缺少了每个answerId对应的响应次数统计，且没有把统计数据和Quiz集合里的答案元数据做合并，按以下方案调整即可：

调整后的完整聚合查询 #

db.quizResults.aggregate([
  // 1. 过滤指定vidId的答题记录
  { "$match": { "vidId": 8225342 } },
  // 2. 按【问题ID+答案ID】分组，统计每个答案的响应次数
  {
    "$group": {
      "_id": {
        questionId: "$questionId",
        answerId: "$answerId"
      },
      "count": { "$sum": 1 },
      "isCorrect": { "$first": "$isCorrect" }
    }
  },
  // 3. 再按问题ID分组，汇总整道题的统计数据，同时收集所有答案的统计结果
  {
    "$group": {
      "_id": "$_id.questionId",
      "correct": {
        "$sum": {
          "$cond": [{ "$eq": ["$isCorrect", true] }, "$count", 0]
        }
      },
      "incorrect": {
        "$sum": {
          "$cond": [{ "$eq": ["$isCorrect", false] }, "$count", 0]
        }
      },
      "totalAnswers": { "$sum": "$count" },
      "answerStats": {
        "$push": {
          "answerId": "$_id.answerId",
          "numResponses": "$count"
        }
      }
    }
  },
  // 4. 关联Quiz集合取问题和答案的元数据
  {
    "$lookup": {
      "from": "quiz",
      "let": { "questionId": "$_id" },
      "pipeline": [
        { "$match": { "$expr": { "$in": ["$$questionId", "$questions._id"] } } },
        { "$unwind": "$questions" },
        { "$match": { "$expr": { "$eq": ["$questions._id", "$$questionId"] } } },
        {
          "$project": {
            "questionNum": "$questions.questionNum",
            "questionName": "$questions.questionName",
            "answers": "$questions.answers",
            "_id": 0
          }
        }
      ],
      "as": "quizData"
    }
  },
  { "$unwind": "$quizData" },
  // 5. 合并答案元数据和统计数据，生成最终的answers数组
  {
    "$project": {
      "_id": 0,
      "questionNum": "$quizData.questionNum",
      "questionName": "$quizData.questionName",
      "correct": 1,
      "incorrect": 1,
      "totalAnswers": 1,
      "answers": {
        "$map": {
          "input": "$quizData.answers",
          "as": "ans",
          "in": {
            "answerId": "$$ans.answerId",
            "answerName": "$$ans.answerName",
            "numResponses": {
              "$let": {
                "vars": {
                  "matchedStat": {
                    "$first": {
                      "$filter": {
                        "input": "$answerStats",
                        "cond": { "$eq": ["$$this.answerId", "$$ans.answerId"] }
                      }
                    }
                  }
                },
                "in": { "$ifNull": ["$$matchedStat.numResponses", 0] }
              }
            }
          }
        }
      }
    }
  }
])

核心改动说明 #

1. 新增两层分组逻辑：先按questionId+answerId分组统计每个答案的响应次数，再按questionId分组汇总整道题的对错总数、总答题数，同时把所有答案的统计结果存储为数组
2. 最后$project阶段通过$map+$filter组合，把Quiz集合里的原始答案和统计到的响应次数做关联，就算没有用户选择的答案也会自动赋值numResponses: 0，完全匹配你需要的输出格式

内容的提问来源于stack exchange，提问作者Bilal Maher