Using find in a array of objects Schema using Mongoose

Question

Am having the following Mongoose Schema in my Node Application

var expenseSchema = new Schema({
    particular : String,
    date : {type : Date, default: Date.now},
    paid_by : String,
    amount : Number,
    month : String
});

var roomSchema = new Schema({
    name: String,
    admin: String,
    roomies : [String],
    expenses : [expenseSchema]
},{
    collection : 'rooms'
});

I need to get expenses for a room for a particular month using find(). Here is what I was trying, however it returns me the whole room object

Room.findOne({_id : req.params._id, 'expenses.month' : 'oct'}).exec(function(err, result){
        if(result == null) {
            res.json({result : 'Oops! We couldn\'t find any rooms...'});            
        } else if(err) {
            res.json({result : 'Error in getting Rooms'});
        } else {
            res.json({result : result});
        }
    });

Can someone help me with this?

chridam · Accepted Answer · 2016-08-30T15:02:39.663

You can use the positional $ operator in your projection to return the matching expenses array element in your result:

Room.findOne(
    { "_id": req.params._id, "expenses.month": "oct" }, 
    { "expenses.$": 1 }  
).exec(callback);

or for multiple matches you could use the aggregation framework's $filter operator in the $project pipeline as:

var ObjectId = mongoose.Types.ObjectId;
Room.aggregate()
    .match({ "_id": new ObjectId(req.params._id), "expenses.month": "oct" }) 
    .project({ 
        "expenses": {
            "$filter": {
                "input": "$expenses", 
                "as": "item",
                "cond": { "$eq": [ "$$item.month", "oct" ] }
            }
        }
    })
    .exec(callback);

or if you are using an older version of Mongoose driver that does not have support for the MongoDB 3.2 $filter, you could use $map alongside $setDifference, where you can "filter" array contents without using $unwind:

var ObjectId = mongoose.Types.ObjectId;
Room.aggregate([
    { "$match": { "_id": new ObjectId(req.params._id), "expenses.month": "oct" } },        
    { "$project": { 
        "expenses": {
            "$setDifference": [
                {
                    "$map": {
                        "input": "$expenses",
                        "as": "items",
                        "in": {
                            "$cond": [
                                { "$eq": [ "$$items.month", "oct" ] },
                                "$$items",
                                false
                            ]
                        }
                    }
                },
                [false]
            ]
        }
    } }
], function(err, results){          
    console.log(JSON.stringify(results[0], null, 4));
});

This returns only right data. But it returns only one object. How can I get all the objects in the array with the same month? — Mohammad Rayan, Aug 30 '16 at 14:31
I think I know why, it's because with `aggregate()` function for `$match` to match by `_id`, values must be cast to `ObjectId` first. Check my updated revision. — chridam, Aug 30 '16 at 15:04

score 0 · Answer 2 · answered Aug 30 '16 at 14:47

Assuming this can work

var expenseSchema = new Schema({
    particular : String,
    date : {type : Date, default: Date.now},
    paid_by : String,
    amount : Number,
    month : String
});

var roomSchema = new Schema({
    name: String,
    admin: String,
    roomies : [String],
    expense_id :  type: Schema.Types.ObjectId, ref: 'expenseSchema'
},{
    collection : 'rooms'
});



Room.findOne({expense_id: req.params._id , "expenses.month": "oct" })
                    .populate('expense_id')
                    .exec(function (err, doc) {
                        if (err) {
                            throw err
                        }
                        console.log(doc)
                        })

Since I've written all POST methods it would be great if there could be a solution without having to change the Schema. Can you suggest a solution with the same schema? — Mohammad Rayan, Aug 30 '16 at 14:53

gianlucatursi · Answer 3 · 2016-08-31T08:51:47.943

0

Room.findOne({
               _id : req.params._id, 
               'expenses.month':'oct'
             },{
              'expenses': { 
                 $elemMatch:{
                   'month':'oct'
                 }
               }
            ).exec(
              function(err, result){
                 if(result == null) {
                     res.json({result : 'Oops! We couldn\'t find any rooms...'});            
                 } else if(err) {
                     res.json({result : 'Error in getting Rooms'});
                 } else {
                     res.json({result : result});
                 }
             });

edited Aug 31 '16 at 08:51

answered Aug 30 '16 at 18:23

gianlucatursi

670
5
19

no luck with this either. – Mohammad Rayan Aug 31 '16 at 07:30
why? `$elemMatch` working great and should be correct. – gianlucatursi Aug 31 '16 at 08:15
it's still returning me with data of month==sept as well. – Mohammad Rayan Aug 31 '16 at 08:43
ahhh ok.. you wanna only 'sept' expenses. Ok. i edit the answer. – gianlucatursi Aug 31 '16 at 08:48
@MohammadRayan this working fine. BUT please use `aggregation` framework that is more efficient. – gianlucatursi Aug 31 '16 at 08:52

Using find in a array of objects Schema using Mongoose

3 Answers3