CODEWITHSUNDEEP
mongodbmongoosemongodb-query
ke.np
ke.np

Reputation: 107

Query documents with condition on array field in mongoose

I have a mongoose schema like this:

var schema = new Schema({
     expenses: [{name: String, cost: number}]
});

schema.virtual('totalCost').get(function() {
    let totalCost = 0;
    this.expenses.forEach((expense)=> {
        totalCost += expense.cost;
    })

    return totalCost;
})

Now I want to query all trips that have totalCost between minCost and maxCost. Is there any way I can do that? I tried $where but I can't pass minCost, maxCost to the function.

Upvotes: 1

Views: 591

Answers (2)

Rahul Kumar
Rahul Kumar

Reputation: 2831

I think $where is the only way, you can achieve that but $where is costly.

I am not sure what are the options in mongoose to use $where but it would be something like below

var minCost=1000;
var maxCost = 10000;
query.$where(function () {
  var cost=0;
  for(var i in this.expenses){cost+=this.expenses[i].cost}
  if(cost>minCost && cost<maxCost){return true;}
})

Here is the link

Another way is we can pass the javascript as a string in $where clause 

var minCost=1000;
var maxCost = 10000;
var func = 'var cost=0;for(var i in this.expenses{cost+=this.expenses[i].cost}if(cost>'+minCost+' && cost<'+maxCost+'){return true;}'
query.$where(func);

Upvotes: 1

chridam
chridam

Reputation: 103365

You can't query against a Mongoose virtual property as they only exist in the Mongoose model representation of documents, not in MongoDB itself where the query should be executed against.

You can however run an aggregation query that calculates the total cost and then use the $match pipeline to query the documents. The following example shows this approach:

Trip.aggregate([
    { "$unwind": "$expenses" },
    {
        "$group": {
            "_id": "$_id",
            "totalCost": { "$sum": "$expenses.cost" },
            "expenses": { "$push": "$expenses" }
        }
    },
    { 
        "$match": { 
            "totalCost": {
                "$gte": minCost,
                "$lte": maxCost
            }
        }
    }           
]).exec(callback);

Upvotes: 2

Related Questions