CODEWITHSUNDEEP
mongodbmongodb-querynosql
kscott
kscott

Reputation: 1946

How to select where sum of fields is greater than a value in MongoDB

Using MongoDB, How would I write this regular SQL statement?

SELECT * FROM table WHERE (field1+field2+field3) > 1

I've been messing with $group, $project, $add, etc. I feel like I'm dancing all around the solution but can't figure it out.

Upvotes: 6

Views: 7516

Answers (3)

Magnus
Magnus

Reputation: 91

It's an old post but this might help someone looking for other solutions. I found this to be even simpler than both $where and .aggregate().

> db.foo.insert({"a": 17, "b": 8})
> db.foo.find({$expr: {$gt: [{$add: ["$a", "$b"]}, 25]}})   // no result
> db.foo.find({$expr: {$gt: [{$add: ["$a", "$b"]}, 24]}})
{ "_id" : ObjectId("602acf55a69fb564c11af7db"), "a" : 17, "b" : 8 }

Upvotes: 3

wdberkeley
wdberkeley

Reputation: 11671

> db.test.drop()
> db.test.insert({ "_id" : 0, "a" : 1, "b" : 1, "c" : 1 })
> db.test.insert({ "_id" : 1, "a" : 1, "b" : 1, "c" : 2 })
> db.test.aggregate([
    { "$project" : { 
        "sum" : { "$add" : ["$a", "$b", "$c" ] } 
    } }, 
    { "$match" : { 
        "sum" : { "$gte" : 4 } 
    } }
])
{ "_id" : 1, "sum" : 4 }

Upvotes: 5

Salvador Dali
Salvador Dali

Reputation: 222751

The easiest way to do this is by using $where (I am not telling that it is not possible to do this with aggregation)

db.table.find({$where: function() {
   return this.field1 + this.field2 + this.field3 > 1
   // most probably you have to handle additional cases if some of the fields do not exist.
}}

The pros of it is that it is easy and intuitive, whereas cons:

requires that the database processes the JavaScript expression or function for each document in the collection.

If you need to perform this kind of searches often, I would go ahead and create a new field which will have a sum of 3 fields stored in it and put an index on it. The downside is that you have to increase your app logic.

Upvotes: 5

Related Questions