Facing a problem with the lookup in the second (student) table that matches all incoming output records mongodb aggregation

Question

I'm facing a problem with the lookup in the second (student) table that matches all incoming output records of the first(test) table. I have two collections "tests" and "students". "Test" collection contains all school tests and the "student" table contains student's attended tests. Student table contains "pastTest"(test attended in past with status "pass" or "fail")array. I want to retrieve student who passed all incoming tests (we retrieve from the tests table)

test table: _id (primary ket) student.pastTests.testId (need to match with test._id)

Test Document:

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

Student Document:

{
    "_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",
}

Query:

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: [
            
        ]
      }
    }
])

Note: Initial match query returns all tests of the term-1, now I have to retrieve students who passed in all tests of the term-1.

Lookup stage is pending - facing problem with lookup in second (student) table who match all incoming output records of first(test) collection

Thanks in advance !!

Answer 1

Try this:

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"
        }
    }
]);

Output:

{
    "_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"
                }
            ]
        }
    ]
}

This how my tests collection looks like

/* 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"
}

This is how my students collection looks like:

/* 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"
        }
    ]
}

Also: In your first $match stage, $and operator is redundant inside $or array it should be like this:

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