在第二个(学生)表中查找匹配所有传入输出记录的mongodb聚合时遇到问题

Question

我遇到了与第一个（测试）表的所有传入输出记录匹配的第二个（学生）表中的查找问题。我有两个集合“测试”和“学生”。“测试”集合包含所有学校测试，“学生”表包含学生参加的测试。学生表包含“pastTest”（过去参加过的测试，状态为“通过”或“失败”）数组。我想检索通过所有传入测试的学生（我们从测试表中检索）

测试表：_id（初级ket）student.pastTests。testId（需要与test._id匹配）

测试文档:

{
    "_id" : ObjectId("5c9b5c1005729b2bf23f3290"),
    "testDate" : {
        "term" : 1,
        "week" : 7
    },
    "retestDate" : {
        "term" : 1,
        "week" : 10
    },
    "testOrder" : "1.1",
    "testDateScheduled" : true,
    "retestDateScheduled" : true
}

学生证件：

{
    "_id" : ObjectId("5c92dd994e8e6b2c1647d0d0"),
    "completedYears" : [],
    "firstName" : "Andrew",
    "lastName" : "Jonhson",
    "teacherId" : ObjectId("5bf36b1076696374e65feb4f"),
    "yearGroup" : "0",
    "schoolId" : 40001,
    "currentTest" : ObjectId("5c9b5c1005729b2bf23f3290"),
    "pastTests" : [ 
        {
           
            "_id" : ObjectId("5d3570645045863d373f6db1"),
            "testId" : ObjectId("5c9b5c1005729b2bf23f3290"),
            "status" : "pass"
        }, 
        {
           
            "_id" : ObjectId("5d425af07708f5636c3bec1c"),
            "testId" : ObjectId("5c9b5fc460e39c2c58e44109"),
            "status" : "pass"
        }, 
        {
            "_id" : ObjectId("5d5e54a875fab079f4d03570"),
            "testId" : ObjectId("5c9b6492bb581c2ceb553fef"),
            "status" : "fail"
        }, 
    ],
    "createdAt" : ISODate("2019-03-21T00:40:57.401Z"),
    "updatedAt" : ISODate("2020-09-24T19:55:38.291Z"),
    "__v" : 0,
    "holdTests" : [],
    "completedTests" : [],
    "className" : "dd",
}

查询：

db.getCollection('tests').aggregate([
     {
       $match: {
           yearGroup: '-1',
            $or : [
                {
                   $and: [
                    {'retestDateScheduled': true},
                    { 'retestDate.term': { $lt: 4 } },
                   ]
                },
                {
                   $and: [
                    {'testDateScheduled': true},
                    { 'testDate.term': { $lt: 4 } },
                   ]
                }
            ]
       }
     },
    {
      $lookup: {
        from: 'students',
        let: {testId: '$_id', schoolId: 49014, yearGroup: '-1'},
        pipeline: [
            
        ]
      }
    }
])

注意:初始匹配查询返回term-1的所有测试，现在我必须检索通过term-1所有测试的学生。

查找阶段待定-在第二个(学生)表中查找匹配第一个(测试)集合的所有传入输出记录时面临问题

提前致谢！！

Answer 1

试试这个：

db.tests.aggregate([
    {
        $match: {
            // Your match condition
        }
    },
    {
        $group: {
            _id: null,
            term_1_testIds: { $push: "$_id" },
            test_count: { $sum: 1 }
        }
    },
    {
        $lookup: {
            from: "students",
            let: { term_1_testIds: '$term_1_testIds', schoolId: 40001, totalTestCount: "$test_count" },
            pipeline: [
                {
                    $match: {
                        $expr: { $eq: ["$schoolId", "$$schoolId"] }
                    }
                },
                { $unwind: "$pastTests" },
                {
                    $match: {
                        "pastTests.status": "pass",
                        $expr: { $in: ["$pastTests.testId", "$$term_1_testIds"] }
                    }
                },
                {
                    $group: {
                        _id: "$_id",
                        firstName: { $first: "$firstName" },
                        yearGroup: { $first: "$yearGroup" },
                        schoolId: { $first: "$schoolId" },
                        currentTest: { $first: "$currentTest" },
                        passedTestCount: { $sum: 1 },
                        pastTests: { $push: "$pastTests" }
                    }
                },
                {
                    $match: {
                        $expr: { $eq: ["$passedTestCount", "$$totalTestCount"] }
                    }
                }
            ],
            as: "students"
        }
    }
]);

输出：

{
    "_id" : null,
    "term_1_testIds" : [
        ObjectId("5c9b5c1005729b2bf23f3290"),
        ObjectId("5c9b5fc460e39c2c58e44109"),
        ObjectId("5c9b6492bb581c2ceb553fef")
    ],
    "test_count" : 3,
    "students" : [
        {
            "_id" : ObjectId("5c92dd994e8e6b2c1647d0d1"),
            "firstName" : "Dheemanth",
            "yearGroup" : "0",
            "schoolId" : 40001,
            "currentTest" : ObjectId("5c9b5c1005729b2bf23f3290"),
            "passedTestCount" : 3,
            "pastTests" : [
                {
                    "_id" : ObjectId("5d3570645045863d373f6db1"),
                    "testId" : ObjectId("5c9b5c1005729b2bf23f3290"),
                    "status" : "pass"
                },
                {
                    "_id" : ObjectId("5d425af07708f5636c3bec1c"),
                    "testId" : ObjectId("5c9b5fc460e39c2c58e44109"),
                    "status" : "pass"
                },
                {
                    "_id" : ObjectId("5d5e54a875fab079f4d03570"),
                    "testId" : ObjectId("5c9b6492bb581c2ceb553fef"),
                    "status" : "pass"
                }
            ]
        }
    ]
}

这就是我的测试集合的样子

/* 1 createdAt:3/27/2019, 5:24:58 PM*/
{
    "_id" : ObjectId("5c9b6492bb581c2ceb553fef"),
    "name" : "Test 3"
},

/* 2 createdAt:3/27/2019, 5:04:28 PM*/
{
    "_id" : ObjectId("5c9b5fc460e39c2c58e44109"),
    "name" : "Test 2"
},

/* 3 createdAt:3/27/2019, 4:48:40 PM*/
{
    "_id" : ObjectId("5c9b5c1005729b2bf23f3290"),
    "name" : "Test 1"
}

这是我的< code >学生集合的样子:

/* 1 createdAt:3/21/2019, 6:10:57 AM*/
{
    "_id" : ObjectId("5c92dd994e8e6b2c1647d0d1"),
    "firstName" : "Dheemanth",
    "yearGroup" : "0",
    "schoolId" : 40001,
    "currentTest" : ObjectId("5c9b5c1005729b2bf23f3290"),
    "pastTests" : [
        {
            "_id" : ObjectId("5d3570645045863d373f6db1"),
            "testId" : ObjectId("5c9b5c1005729b2bf23f3290"),
            "status" : "pass"
        },
        {
            "_id" : ObjectId("5d425af07708f5636c3bec1c"),
            "testId" : ObjectId("5c9b5fc460e39c2c58e44109"),
            "status" : "pass"
        },
        {
            "_id" : ObjectId("5d5e54a875fab079f4d03570"),
            "testId" : ObjectId("5c9b6492bb581c2ceb553fef"),
            "status" : "pass"
        }
    ]
},

/* 2 createdAt:3/21/2019, 6:10:57 AM*/
{
    "_id" : ObjectId("5c92dd994e8e6b2c1647d0d0"),
    "firstName" : "Andrew",
    "yearGroup" : "0",
    "schoolId" : 40001,
    "currentTest" : ObjectId("5c9b5c1005729b2bf23f3290"),
    "pastTests" : [
        {
            "_id" : ObjectId("5d3570645045863d373f6db1"),
            "testId" : ObjectId("5c9b5c1005729b2bf23f3290"),
            "status" : "pass"
        },
        {
            "_id" : ObjectId("5d425af07708f5636c3bec1c"),
            "testId" : ObjectId("5c9b5fc460e39c2c58e44109"),
            "status" : "pass"
        },
        {
            "_id" : ObjectId("5d5e54a875fab079f4d03570"),
            "testId" : ObjectId("5c9b6492bb581c2ceb553fef"),
            "status" : "fail"
        }
    ]
}

另外：在您的第一个$match阶段，$和运算符在$或数组中是多余的，它应该是这样的：

{
    $match: {
        yearGroup: '-1',
        $or: [
            {
                'retestDateScheduled': true,
                'retestDate.term': { $lt: 4 }
            },
            {
                'testDateScheduled': true,
                'testDate.term': { $lt: 4 }
            }
        ]
    }
}