Mongodb Query that relies on same document

Question

So I'm trying to write a query in a MongoDB that relies on part of the document being queried to return the document I want.

This is an example of the format of those documents:

{
    "_id" : ObjectId("58990510cdab041b39c78dd1"),
    "classcode" : "CS433",
    "department" : "CS",
    "instructor" : {
            "name" : "Mike",
            "office" : "Starbucks"
    },
    "students" : [
            {
                    "name" : "Dave",
                    "major" : "CS",
                    "gradyear" : 2019
            },
            {
                    "name" : "Joe",
                    "major" : "CS",
                    "gradyear" : 2018
            },
            {
                    "name" : "Stan",
                    "major" : "CS",
                    "gradyear" : 2017
            }
    ]
}

I want to use the string that denotes the department to help see if there is a not a match for that department in major. i.e. if the department is CS then it checks to see if there is a student that does not have CS as their major.

I'm aware of $ne, $elemMatch, and what not. I'm just having trouble using another part of the document to help the query. I don't think a sub-query will be of use here.

Answer 1

You can use $redact.

$redact to go through a document level at a time and look for major field recursively and perform $$DESCEND and $$PRUNE on the criteria from the $$ROOT level.

The query will keep all the students who doesn't have major matching department.

db.collection.aggregate([{
    "$redact": {
        "$cond": [{
                $ne: ["$major", "$$ROOT.department"]
            },
            "$$DESCEND",
            "$$PRUNE"
        ]
    }
}])

Update:

This query will return all the documents when there is at least one student with major not matching the department.

db.collection.aggregate([
    { $redact: {
        $cond: {
            if: { "$ifNull" : ["$department", false] },
            then: { $cond: {
                if: { $anyElementTrue: {
                    $map: {
                        input: "$students",
                        as: "student",
                        in: { $ne: [ "$$student.major", "$$ROOT.department" ] }
                    }
                }},
                then: "$$DESCEND",
                else: "$$PRUNE"
            }},
            else: "$$DESCEND"
        }
    }}
])