0

I have some document like this

db.instructors.insertMany( [
{
"name": "A",
"dept_name": "CS",
"salary": 68000,
"teaches": [ { course_id: "CS-101", year: 2018 }, { course_id: "CS-347", year: 2018 } ]
},
{
"name": "C",
"dept_name": "Finance",
"salary": 90000,
"teaches": [ { course_id: "FIN-201", year: 2016 }, { course_id: "FIN-301", year: 2017 }, { course_id: "FIN-320",
year: 2017 } ]
},
{
"name": "B",
"dept_name": "Biology",
"salary": 72000,
"teaches": [ { course_id: "BIO-101", year: 2016 }, { course_id: "BIO-201", year: 2017 }, { course_id: "BIO-101",
year: 2018 } ]
}
] );

I have to find the name, department name, and the salary of each instructor who has salary higher than 75,000 and has taught 2 courses. If an instructor has taught the same course N times, it is counted as N courses. I figured out that I have to use aggregate, but I cannot display the department name and salary of each instructor. Here my code:

db.instructors.aggregate([{
   $match: {
     salary: {$gt: 75000}
   }},
   { $unwind: "$teaches" },
   {
     $group:
       {
         _id: "$name",
         totalCourses:
           {
             $sum: 1
           }
       }
   },
   {
     $match: {
       totalCourses: 2
     }
   }

 ]);
Improve this question
1
  • Is the criteria "has taught exactly 2 courses" or "has taught at least 2 courses" Commented Jul 10, 2021 at 21:00

2 Answers 2

Reset to default
1

You can do this in a single query using the $size operator:

db.instructors.find({ salary: { $gt: 75000 }, teaches: { $size: 2 }})
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

0

modify this :

 _id: "$name"

to

 _id: {"$name" , "$dept_name" , "$salary"}

or you can do the better:

 db.instructors.aggregate([ {$match:{salary:{$gt:75000}}  }  , {   $project: { name:1,"dept_name":1  ,"salary":1 ,  numberOfCourses: { $cond: { if: { $isArray: "$teaches" }, then: { $size: "$teaches" }, else: "NA"} }       }    } ,{$match:{"numberOfCourses":2}}    ])

to avoid the "expensive" unwind operation ...

Improve this answer

Comments