How to find Sub-Documents by Criteria + Mongoose / MongoDB

Question

Following are two 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'
    },
    statusId: {
        type: Schema.Types.ObjectId,
        ref: 'status'
    }
});

EmployeeDetailSchema data gets saved on demand, like when a particular status is assigned to Employee. In that case, 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.

UseCase :

I want to fetch all Employees who are tagged with particular status. Say input Status ID is 1234 then i want to fetch all employees whose status id is 1234 in EmployeeDetail document.

Following is the approach which i tried using Mongoose :

exports.getEmployeesByStatus = function (req, res) {
    console.log('Status ID : ' + req.query.statusId);

    EmployeeModel.find({'employeeDetailsId.statusId': {$eq: mongoose.Types.ObjectId(req.params.statusId)}})
        .exec(function (err, result) {
            if (err)res.send('400', {message: 'Unable to fetch employees data by status. Please try again later'});

            res.jsonp(result);
        });
};

Result that is returned is empty array though there are employees who are assigned to some statuses. Is my querying approach with Mongoose right ?

are you using sub document or using reference of employee details model for `employeeDetailsId`? — Shaishab Roy, Dec 01 '16 at 07:56
Couple of other good questions about MongoDB : Finding Sub-Documents by Criteria : [One](http://stackoverflow.com/questions/16845191) and [Two](http://stackoverflow.com/questions/21142524). Thought it might help others. — BeingSuman, Dec 07 '16 at 09:52

Shaishab Roy · Accepted Answer · 2016-12-01T11:57:36.830

2

You are using reference of EmployeeDetailSchema for employeeDetailsId according to your schema design. so you can't directly compare reference model field without populate. you should populate first then compare and filter documents or can user aggregate function achieve your goal.

can try this one:

EmployeeModel.aggregate([
      {$lookup: {from: 'employeedetails', localField: 'employeeDetailsId',foreignField: '_id',as: 'details'}},
      {$match: { $and:[{ "details.statusId": { "$exists": true } },{"details.statusId": req.params.statusId}]} }
    ]).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);
        });

N.B: convert String to ObjectId the value of req.params.statusId

{$match: {$and: [{"details.statusId": {"$exists": true}}, {"details.statusId": mongoose.Types.ObjectId(req.params.statusId)}]}}

edited Dec 01 '16 at 11:57

answered Dec 01 '16 at 08:13

Shaishab Roy

16,335
7
50
68

thanks for the reply man, i dint even knew about `$lookup`. Went through MongoDB documents about the same n found your answer very valid to my usecase. But running the same fetched no result, its still same empty [] result. Just wanted to check if Mongoose supports `aggregate` with `$lookup` ? Any reference documents plz. – BeingSuman Dec 01 '16 at 08:46
I used it as simple query in MongoDB terminal by excluding last check in match, it gave me desired result : `db.getCollection('employees').aggregate([ { $lookup: { from: "employeedetails", localField: "employeeDetailsId", foreignField: "_id", as: "details" } }, { $match: { $and:[{ "details.statusId": { "$exists": true }}]} } ]);` – BeingSuman Dec 01 '16 at 09:04
But the issue with previous query is : i have removed condition check for `{"details.statusId": req.params.statusId}`. On adding that it again gives same old problem : `Fetched 0 record(s)` – BeingSuman Dec 01 '16 at 09:09
can you add sample document ? – Shaishab Roy Dec 01 '16 at 09:15
/* Employee Document */ `{ "_id" : ObjectId("583f114e1cff44b7ab414dc1"), "employeeId" : "10020", "employeefirstname" : "Tom", "experienceyears" : "15", "employeeDetailsId" : ObjectId("583fbbfe78854dd424f0523f") }` /* Employee Details Document */ `{ "_id" : ObjectId("583fbbfe78854dd424f0523f"), "employeeId" : ObjectId("583f114e1cff44b7ab414dc1"), "statusId" : ObjectId("583ee05a1d5161941632091a"), "__v" : 0 }` – BeingSuman Dec 01 '16 at 09:20
Following was the query which Fetched me 0 records : `db.getCollection('employees').aggregate([ { $lookup: { from: "employeedetails", localField: "employeeDetailsId", foreignField: "_id", as: "details" } }, { $match: { $and:[{ "details.statusId": { "$exists": true }}, { "details.statusId": "583ee05a1d5161941632091a"}]} } ]);` – BeingSuman Dec 01 '16 at 09:21
if you are using `{$match: { "details.statusId": { "$exists": true } } }` then are you getting empty? – Shaishab Roy Dec 01 '16 at 09:21
ensure that your collection name is correct for `from: "employeedetails"` – Shaishab Roy Dec 01 '16 at 09:26
As i mentioned in my second comment : collection name is not a problem as am getting desired result when i just remove second condition in `$match:{$and}`. Only problem i see is adding `{ "details.statusId": "583ee05a1d5161941632091a"}` – BeingSuman Dec 01 '16 at 09:37
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/129511/discussion-between-shaishab-roy-and-beingsuman). – Shaishab Roy Dec 01 '16 at 09:39

How to find Sub-Documents by Criteria + Mongoose / MongoDB

1 Answers1