How to match Array elements in Sub-Documents by Criteria

Question

I have following Mongoose schemas :

EmployeeSchema :

var EmployeeSchema = new Schema({
    name : String,
    employeeDetailsId: {
        type: Schema.Types.ObjectId,
        ref: 'employeedetails'
    }
});

EmployeeDetailSchema :

var EmployeeDetailSchema = new Schema({
    employeeId: {
        type: Schema.Types.ObjectId,
        ref: 'employee'
    },
    primarySkills: [
    {
        type: Schema.Types.ObjectId,
        ref: 'skills'
    }
],
});

SkillsSchema :

var SkillsSchema = new Schema({
    name: {
        type: String,
        required: true
    }
});

EmployeeDetailSchema data gets saved on demand, like when a particular Skill is assigned to Employee. Once EmployeeDetail document is saved then corresponding EmployeeDetailID is saved back to EmployeeSchema as employeeDetailsId.

Now there is bi-directional relationship between EmployeeSchema and EmployeeDetailSchema.

NOTE : Multiple Skills can be associated to an Employee and they are stored as an array of ObjectID's in EmployeeDetails Schema.

UseCase :

I want to fetch all Employees who have particular Skill associated with them, Skill will be input to the Mongoose / Mongo query.

Say input Skill ID is 1234 then i want to fetch all employees who have Skill id 1234 in EmployeeDetail > PrimarySkills array.

Following is the approach which i tried using Mongoose :

EmployeeModel.aggregate([
    {
        $lookup: {
            from: 'employeedetails', 
            localField: 'employeeDetailsId',
            foreignField: '_id',
            as: 'details'
        }
    },
    {
        $match: { 
            $and: [
                { "details.primarySkills": { "$exists": true } },               
                { 
                    "details.primarySkills": { 
                        $in: [mongoose.Types.ObjectId(req.params.skillId)]                          
                    }
                }
            ]
        } 
    }
]).exec(function (err, result) {
    if (err) return res.send('400', {
        message: 'Unable to fetch employees data by status. Please try again later'
     });

     return res.jsonp(result);
});

Result : Empty array.

I have no clue where am going wrong, need some help.

score 1 · Accepted Answer · edited May 23 '17 at 12:24

My bad, original approach which i followed was all fine except a small mistake. I should have used req.query.skillId instead of req.params.skillId

For those wondering the difference b/w query and params, check this answer

This is the final solution, thought it may help others :

EmployeeModel.aggregate([
    {
        $lookup: {
            from: 'employeedetails', 
            localField: 'employeeDetailsId',
            foreignField: '_id',
            as: 'details'
        }
    },
    {
        $match: { 
            $and: [
                { "details.primarySkills": { "$exists": true } },               
                { 
                    "details.primarySkills": { 
                        $in: [mongoose.Types.ObjectId(req.query.skillId)]                          
                    }
                }
            ]
        } 
    }
]).exec(function (err, result) {
    if (err) return res.send('400', {
        message: 'Unable to fetch employees data by status. Please try again later'
     });

     return res.jsonp(result);
});

chridam · Answer 2 · 2016-12-08T08:42:27.737

One approach that you could take is apply the $lookup to the Skills model on the EmployeeDetails model first and then do another lookup to the Employee model

EmployeeDetails.aggregate([
    { "$match": { "primarySkills": req.query.skillId } },
    { "$unwind": "$primarySkills" }, // skip this pipeline step for MongoDB 3.4 and above
    {
        "$lookup": {
            "from": "skills",// ensure this is the correct collection name
            "foreignField": "_id",
            "localField": "primarySkills",
            "as": "skills"
        }
    },
    { "$unwind": "$skills" }
    {
        "$group": {
            "_id": "$_id",
            "employeeId": { "$first": "$employeeId" },
            "primarySkills": { "$push": "$skills" }
        }
    },
    {
        "$lookup": {
            "from": "employees",// ensure this is the correct collection name
            "foreignField": "_id",
            "localField": "employeeId",
            "as": "employee"
        }
    }
]).exec(callback);

How to match Array elements in Sub-Documents by Criteria

2 Answers2