Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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.

like image 791
kscott Avatar asked Apr 25 '15 02:04

kscott


1 Answers

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.

like image 110
Salvador Dali Avatar answered Oct 12 '22 01:10

Salvador Dali