mongodb find by comparing field values

问题:

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

回答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.

回答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.

回答3:

You can use a $where. Just be aware it will be fairly slow (has to execute Javascript code on every record) so combine with indexed queries if you can.

db.T.find( { $where: function() { return this.Grade1 > this.Grade2 } } );

or more compact:

db.T.find( { $where : "this.Grade1 > this.Grade2" } );