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.

s7vr · Answer 1 · 2017-02-07T02:02:41.397

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

edited Feb 07 '17 at 02:02

answered Feb 07 '17 at 00:31

s7vr

73,656
11
106
127

This returns only the whole document but without the documents of the various students in the array. I'm looking to return all of the documents where at least one of the students' major are not the same as the department of the class. – Megumi Ai Feb 07 '17 at 01:17
Can you please try the updated query ? and please add the expected response to the post if this doesnt work the way you intend. – s7vr Feb 07 '17 at 01:43
The updated query returns the correct object now. Do you know of any possible ways to execute this without having to use the aggregation function but with the find function instead? I was told the query to find my answer was supposed to be quite simple and can be done without the aggregation function. – Megumi Ai Feb 07 '17 at 02:25
Sure. You can embed javascript in the `find` function using `$where` operator. Take a look at this.http://stackoverflow.com/questions/41878722/find-element-based-on-two-values/41881637#41881637. Here is the equivalent based ont that answer. `db.collection.find({ "$where": function() { self = this; return this.students.filter(function(student) { return self.department !== student.major; }).length > 0 } })` – s7vr Feb 07 '17 at 02:36

Mongodb Query that relies on same document

1 Answers1