mongodb find by comparing field values

Question

Is it possible to express the following SQL query in mongodb:

SELECT * FROM table AS t WHERE t.field1 > t.filed2;

edit: To summarize:.

1. using a third field storing "field1 - field2" is almost perfect, but requires a little extra maintenance.
2. $where will load and eval in JavaScript and won't use any indexes. No good for large data.
3. map/reduce has the same problem and will go trough all records even if we need only one

Answer 1

You can do this using $where:

db.coll.find( { $where: "this.field1 > this.field2" } );

But:

Javascript executes more slowly than the native operators, but it is very flexible

If performance is an issue better to go with way suggested by @yi_H.

Answer 2

You could store in your document field1 - field2 as field3, then search for { field3: { $gt: 0 } }

It also possible to get matching documents with mapreduce.