javascript - 通过嵌套数组查找猫鼬组
问题描述
我有两个模式,第一个是 Quiz,另一个是 quizResults,我想在 quiz Schema 中进行查找时从 quizResult 获取聚合数据。下面是我的测验模式:
vidId: {type: Number, required: true},
status: {type: Number, enum: [statusType.quizStatusEnums.LIVE, statusType.quizStatusEnums.DELETED], default: statusType.quizStatusEnums.LIVE},
questions: [
{
questionNum: { type: Number },
questionName: { type: String },
answers: [
{
answerId: { type: String, default: uuid.v4() },
answerName: { type: String },
isCorrect: { type: Boolean },
answerType: { type: Number, enum: [statusType.quizTypeEnums.QUIZ, statusType.quizTypeEnums.SURVEY], default: statusType.quizTypeEnums.QUIZ},
hotspotId: { type: Number },
overlayId: {type: Number},
panelId: {type: String}
}
]
}
],
第二个是QuizResults:需要对这个集合进行聚合查询。
created: {
type: Date,
},
vidId: {
type: Number,
required: true,
},
viewerId: {
type: String,
required: true,
},
quizId: {
type: Schema.Types.ObjectId,
ref: 'quiz'
},
questionId: {
type: Schema.Types.ObjectId,
ref: 'quiz'
},
answerId: {
type: String,
required: true
},
isCorrect: {
type: Boolean,
default: false,
},
whenAnswered: {
type: Date
},
我想要这样的最终聚合结果:
[
{
"questionNum": 2,
"questionName": "Which is the best selling record in history ?",
"correct": 10,
"incorrect": 20,
"totalAnswers": 30,
"answers": [
{
"answerId": "123abc",
"answerName": "Thriller Michel Jackson",
"numResponses": 10
},
{
"answerId": "234d",
"answerName": "A kind of Magic Queen",
"numResponses": 10
},
{
"answerId": "432e",
"answerName": "help The Beatles",
"numResponses": 10
}
]
},
{
"questionNum": 1,
"questionName": "What value has the number PI?",
"correct": 5,
"incorrect": 3,
"totalAnswers": 8,
"answers": [
{
"answerId": "111",
"answerName": "3.12",
"numResponses": 0
},
{
"answerId": "222",
"answerName": "3.14",
"numResponses": 5
},
{
"answerId": "333",
"answerName": "3.16",
"numResponses": 3
}
]
}
]
我尝试的是:
aggregate([
{ "$match": { "vidId": 8225342, } },
{
"$group": {
"_id": "$questionId",
"Correct": {
"$sum": {
"$cond": [
{ "$eq": ["$isCorrect", true] },
1,
0
]
},
},
"Incorrect": {
"$sum": {
"$cond": [
{ "$eq": ["$isCorrect", false] },
1,
0
]
}
},
}
},
{
"$lookup": {
"from": "quiz",
"let": { "id": "$_id" },
"pipeline": [
{ "$match": { "$expr": { "$in": ["$$id", "$questions._id"] } } },
{ "$unwind": "$questions" },
{ "$match": { "$expr": { "$eq": ["$questions._id", "$$id"] } } },
],
"as": "quizData"
}
},
{ $unwind: '$quizData' },
{ "$project": {
"questionName": "$quizData.questions.questionName",
"questionNum": "$quizData.questions.questionNum",
"Correct": "$Correct",
"Incorrect": "$Incorrect",
"answers": "$quizData.questions.answers" } },
])
我得到了类似的结果:
{
"_id": "611632305bd3910929b95552",
"questionName": "Which is the best selling record in history?",
"questionNum": 5,
"Correct": 3,
"Incorrect": 0,
"answers": [
{
"answerId": "078f441b-373f-40e9-89e1-04fca0a9fc5d",
"answerType": 0,
"_id": "611632305bd3910929b95553",
"answerName": "Thriller Michel Jackson",
"isCorrect": true,
"hotspotId": 470114,
"overlayId": 3,
"panelId": "12abc"
},
{
"answerId": "644b80fe-5778-46fa-b3a6-1eff5989cdee",
"answerType": 0,
"_id": "611632305bd3910929b95554",
"answerName": "A kind of Magic Queen",
"isCorrect": false,
"hotspotId": 470113,
"overlayId": 4,
"panelId": "12345abc"
},
{
"answerId": "5bde2682-66fe-4c79-a728-aea67f6842a8",
"answerType": 0,
"_id": "611632305bd3910929b95555",
"answerName": "help The Beatles",
"isCorrect": false,
"hotspotId": 470112,
"overlayId": 3,
"panelId": "12abc"
}
]
},
我怎样才能得到这样的答案数组:
answers: [
{
answerId: "123abc",
answerName: "Thriller Michel Jackson",
numResponses: 10
},
{
answerId: "234d",
answerName: "A kind of Magic Queen",
numResponses: 10
},
{
answerId: "432e",
answerName: "help The Beatles",
numResponses: 10
}
]
解决方案
您可以尝试以相反的方式接近它。用于过滤和聚合,然后运行$map和$filter$lookup
以获取每个答案的匹配统计信息:quizResults
db.quiz.aggregate([
{
$match: { "vidId": 8225342 }
},
{
$lookup: {
from: "quizResults",
pipeline: [
{ $match: { "vidId": 8225342 } },
{
$group: {
_id: "$answerId",
count: { $sum: 1 }
}
}
],
as: "quizResults"
}
},
{
$project: {
_id: 1,
questions: {
$map: {
input: "$questions",
as: "q",
in: {
_id: "$$q._id",
questionName: "$$q.questionName",
questionNum: "$$q.questionNum",
answers: {
$map: {
input: "$$q.answers",
as: "a",
in: {
$mergeObjects: [
"$$a",
{
$let: {
vars: {
fst: {
$first: {
$filter: { input: "$quizResults", cond: { $eq: [ "$$this._id", "$$a._id" ] } }
}
}
},
in: { numResponses: "$$fst.count" }
}
}
]
}
}
}
}
}
}
}
}
])
推荐阅读
- ruby-on-rails - Ruby on Rails 中的三元运算符
- ios - Firebase - 将应用程序从 iOS 开发人员转移到另一个
- cmake - 我们如何使用 CMAKE 对仅使用标头构建的库进行版本控制?
- python - Python-pptx 无法从 ppt 中删除/删除现有图形
- android - 调用`setX()`后如何恢复正常的子定位行为
- java - kafka-producer-perf-test.sh 有多少生产者?
- html - 如何在固定元素之后定位相对元素
- ruby-on-rails - HABTM:无法创建资源
- solr - MoreLikeThisComponent SOLR 上的 NullPointerException
- ios - 在以编程方式创建的 UIViewController 内具有安全区域的 UITabBar