Mongo field A greater than field B

问题:

i'm trying a simple query in Mongo which in MySQL would look like this.

select * from emails where bounceCount > sentCount;

So far I have.

db.email.find({ bounceCount : { $gt : sentCount } } );

But I get this error

JS Error: ReferenceError: sentCount is not defined (shell):0

How do I reference the sentCount in that shell?

回答1:

Everyone seems to mention $where without really knowing that it is:

• slow
• insecure (evaled)
• JavaScript, not MongoDB internals
• And, on versions prior to 2.4, single threaded and global locked

Another method which would be a lot better for about 99% of cases is to use the aggregation framework:

db.col.aggregate([
    {$project: {ab: {$cmp: ['$bounceCount','$sentCount']}}},
    {$match: {ab:{$gt:0}}}
])

回答2:

db.so.find("this.bounceCount > this.sentCount") is what you are looking for.

Equivalent: db.so.find({"$where":"this.bounceCount > this.sentCount"})

Documentation: http://docs.mongodb.org/manual/reference/operator/where/

Shell output:

> db.so.insert({bounceCount:1, sentCount:2})
> db.so.insert({bounceCount:5, sentCount:3})
> db.so.insert({bounceCount:5, sentCount:4})
> db.so.insert({bounceCount:5, sentCount:7})
> db.so.insert({bounceCount:9, sentCount:7})

> db.so.find()
{ "_id" : ObjectId("516d7f30675a2a8d659d7594"), "bounceCount" : 1, "sentCount" : 2 }
{ "_id" : ObjectId("516d7f37675a2a8d659d7595"), "bounceCount" : 5, "sentCount" : 3 }
{ "_id" : ObjectId("516d7f3b675a2a8d659d7596"), "bounceCount" : 5, "sentCount" : 4 }
{ "_id" : ObjectId("516d7f3d675a2a8d659d7597"), "bounceCount" : 5, "sentCount" : 7 }
{ "_id" : ObjectId("516d7f40675a2a8d659d7598"), "bounceCount" : 9, "sentCount" : 7 }

> db.so.find({"bounceCount":5})
{ "_id" : ObjectId("516d7f37675a2a8d659d7595"), "bounceCount" : 5, "sentCount" : 3 }
{ "_id" : ObjectId("516d7f3b675a2a8d659d7596"), "bounceCount" : 5, "sentCount" : 4 }
{ "_id" : ObjectId("516d7f3d675a2a8d659d7597"), "bounceCount" : 5, "sentCount" : 7 }

> db.so.find("this.bounceCount > this.sentCount")
{ "_id" : ObjectId("516d7f37675a2a8d659d7595"), "bounceCount" : 5, "sentCount" : 3 }
{ "_id" : ObjectId("516d7f3b675a2a8d659d7596"), "bounceCount" : 5, "sentCount" : 4 }
{ "_id" : ObjectId("516d7f40675a2a8d659d7598"), "bounceCount" : 9, "sentCount" : 7 }

回答3:

It should do the trick:

db.emails.find({ $expr: { $gt: [ "$bounceCount" , "$sentCount" ] } });

Here is the reference where I found it: https://docs.mongodb.com/manual/reference/operator/query/expr/#op._S_expr

回答4:

You can use the $where operator to do this, which lets you use Javascript code in the query.

For your example, you would do:

db.email.find({ $where: "this.bounceCount > this.sentCount" });

See the MongoDB documentation page for more details on the $where operator: http://docs.mongodb.org/manual/reference/operator/where/#op._S_where