mongodb - 如何展开进一步包含数组的数组字段

Question

嗨，我有一个userAnswer schema看起来像这样的

var userAnswer = mongoose.Schema({
'questionId' : { 'type' : mongoose.Schema.Types.ObjectId, 'ref': 'Question'},
'userId' : { 'type': mongoose.Schema.Types.ObjectId, 'ref' : 'User'},
'score' : 1 
})

然后一个userAttempt schema包含关于这个问题的尝试

var userAttempt = mongoose.Schema({
'userAnswerId' : { 'type' : mongoose.Schema.Types.ObjectId, 'ref' : 'UserAnswer'},
'attemptData' : [{ 
    'userInputMCQ' : String,
    'time' : Date 
    }],
})

最后usersnapshots schema一个保存关于attemptData字段中每个元素的快照userAttempt Schema

看起来像这样

var userAttemptSnapshot= mongoose.Schema({
userAttemptDataId : {type: mongoose.Schema.Types.ObjectId },
count : {type: Number, default: 1},
userData :[ {
    value: String,
    timeOfCreation : { type : Date, default : Date.now }
    }]
}, 
   {    timestamps: true    }
);

现在我想以这样一种方式获取所有data关于question尝试的信息，即 所有documents领域attempData都是UserAttempt Schema

 'attemptData' : [{ 
    'userInputMCQ' : String,
    'time' : Date 
    }],

attempt以各自的snapshots使用方式作为个人获取aggregation

我aggregation query的是

db.useranswers.aggregate([ 
{ $match : {  'questionId' : ObjectId("5a2dfefd6dc3de029e488961"),'userId' :ObjectId("5a2cea6d6dc3de029e488814") }},
{ '$lookup' : {from: "userattempts",
    localField: "_id",
    foreignField: "userAnswerId",
    as: "attempts"}},
{ '$unwind' : '$attempts'},
{'$unwind' : '$attempts.attemptData'}
//after this stage i will use lookup to get snapshot data but i dont get those attempts as individual documents so that i could use lookup here
})

编辑： 我的userAnswer收藏是

{ 
    "_id":ObjectId("5b4f359244781a45bcc98fcb") , 
    "userId":ObjectId("5a2cea6d6dc3de029e488814"),
    "questionId": ObjectId("5a631937e2509a3288171c37"),
}

UserAttempt集合是

{
    "_id":ObjectId("5b4f359204a7251ebcabef24"),
    "attemptData":[
    {
        "_id" : ObjectId("5b4f359204a7251ebcabef23"),
        "userInputMCQ" : "0"
        "timeOfCreation" : ISODate("2018-07-18T18:11:54.855+05:30"),
    },
    {
        "_id" : ObjectId("5b4f35bb04a7251ebcabef27"),
        "userInputMCQ" : "0",
        "timeOfCreation" : ISODate("2018-07-18T18:12:35.765+05:30"),
    } ]
}

UserAttemptSnapshot collection is
{
    "_id":ObjectId("5b4f359204a7251ebcabef25"),
    "userAttemptId":ObjectId("5b4f359204a7251ebcabef23"),
    "count" : 2,
    "userData" : [ { 
                     'value' : "Some String" ,
                     "_id":ObjectId("5b4f359204a7251ebcabef26")  
                   },
                   { 
                     'value' : "Some String" ,
                     "_id": ObjectId("5b4f359204a7251ebcabef27")  
                   } ]
},
{
    "_id":ObjectId("5b4f359204a7251ebcabef25"),
    "userAttemptId":ObjectId("5b4f359204a7251ebcabef27"),
    "count" : 1,
    "userData" : [ { 
                     'value' : "Some String" ,
                     "_id":ObjectId("5b4f359204a7251ebcabeaee")  
                   },
                 ]
}

我想要这样的输出

{
     "_id":ObjectId("5b4f359244781a45bcc98fcb") , 
    "userId":ObjectId("5a2cea6d6dc3de029e488814"),
    "questionId": ObjectId("5a631937e2509a3288171c37"),
    "attemptData": {
        "_id" : ObjectId("5b4f359204a7251ebcabef23"),
        "userInputMCQ" : "0"
        "timeOfCreation" : ISODate("2018-07-18T18:11:54.855+05:30"),
        "userAttemptId" : ObjectId("5b4f359204a7251ebcabef23"),
        "count" : 2,
        "userData" : [ { 
                 'value' : "Some String" ,
                 "_id":ObjectId("5b4f359204a7251ebcabef26")  
               },
               { 
                 'value' : "Some String" ,
                 "_id": ObjectId("5b4f359204a7251ebcabef27")  
               } ]
    },
},
{
     "_id":ObjectId("5b4f359244781a45bcc98fcb") , 
    "userId":ObjectId("5a2cea6d6dc3de029e488814"),
    "questionId": ObjectId("5a631937e2509a3288171c37"),
    "attemptData": {
          "_id" : ObjectId("5b4f35bb04a7251ebcabef27"),
          "userInputMCQ" : "0",
          "timeOfCreation" : ISODate("2018-07-18T18:12:35.765+05:30"),
          "userAttemptId":ObjectId("5b4f359204a7251ebcabef27"),
          "count" : 1,
          "userData" : [ { 
                 'value' : "Some String" ,
                 "_id":ObjectId("5b4f359204a7251ebcabeaee")  
               },
             ]
    }  
}

标签： mongodbaggregation-framework

Answer 1

您可以在 3.6 中使用以下聚合。

db.useranswers.aggregate([ 
{"$lookup":{
    "from": "userattempts",
    "let": {"id":"$_id"},
    "pipeline":[
      {"$match":{"$expr":{"$eq":["$$id","$userAnswerId"]}}},
      {"$unwind":"$attemptData"},
      {"$lookup":{
        "from": "userattemptsnapshots",
        "localField": "attemptData._id",
        "foreignField":"userAttemptId",
        "as": "attemptData.attemptsnapshot"
      }},
      {"$unwind":"$attemptData.attemptsnapshot"},
      {"$group":{
         "_id":"$userAnswerId",
         "attemptData":{
            "$push":"$attemptData"
         }
      }}
    ],
    "as": "attempts"
 }},
{"$unwind":"$attempts"},
])

更新 3.4 版本

db.useranswers.aggregate([ 
{"$lookup":{
    "from": "userattempts",
    "localField": "_id",
    "foreignField":"userAnswerId",
    "as": "attempts"
}},
{"$unwind":"$attempts"},
{"$unwind":"$attempts.attemptData"},
{"$lookup":{
    "from": "userattemptsnapshots",
    "localField": "attempts.attemptData._id",
    "foreignField":"userAttemptId",
    "as": "attempts.attemptData.attemptsnapshot"
}},
{"$unwind":"$attempts.attemptData.attemptsnapshot"}
])