36

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?

Mitesh
  • 433
  • 1
  • 5
  • 6

4 Answers4

47

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

Andrei Petrik
  • 1,101
  • 1
  • 10
  • 9
  • For those using Amazon DocumentDB, `$expr` and `$where` are not supported yet in April 2022. https://docs.aws.amazon.com/documentdb/latest/developerguide/mongo-apis.html – Noumenon Apr 18 '22 at 03:33
27

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}}}
])
Sammaye
  • 43,242
  • 7
  • 104
  • 146
  • I don't see the "insecure" part of the answer I gave. – 000 Apr 16 '13 at 18:21
  • @JoeFrambach The `$where` param takes a string, much like writing SQL with no escaping library. – Sammaye Apr 16 '13 at 18:22
  • 5
    But it is the developer writing the query. At no time is user input ever evaled – 000 Apr 16 '13 at 18:22
  • 2
    @JoeFrambach Sometimes, what if the stuff put into the $where comes from GET or POST or whatever? I don't think we can guarantee this here, we don't know enough. – Sammaye Apr 16 '13 at 18:23
  • 4
    What if what if what if. It's a straightforward query. I agree with you on all the other fronts though. – 000 Apr 16 '13 at 18:24
  • @JoeFrambach It doesn't matter how simple the query is, `select * from user where; delete *;` is simple but would you allow it? – Sammaye Apr 16 '13 at 18:26
  • @JoeFrambach how do you mean? – Sammaye Apr 16 '13 at 20:03
  • @JoeFrambach Ok, I am gonna assume I know your point here, I agree that if this was only done in the console or written hard coded by the user in the application it would not be insecure however, if you have been on this tag long enough you would realise these questions start in this form and then a follow up question ends with "my system is broken". You say "what if what if what if" and I say: "most likely". I prefer to nip the problem here and now rather than solve the problems your assumptions gave after the fact. – Sammaye Apr 17 '13 at 08:18
  • This aggregate solution is superior to the eval. It will be faster. – superluminary Jul 14 '15 at 21:05
  • Got an idea on how to implement this in document validation? – Moshe Gottlieb Aug 10 '16 at 13:46
  • Not really, I have not really had time to even look into that feature yet – Sammaye Aug 12 '16 at 10:09
  • This seems very unintuitive compared to sql. Looking at this code and not knowing agregate syntax I can't imagine what would be the output. But I can with this syntax: db.email.find({ $where: "this.bounceCount > this.sentCount" }); This is probalby people use it, and prefer it over more - apparently efficient - aggregate – Marcin Konrad Ceglarek Oct 11 '16 at 10:33
  • If they prefer that then they do not understand how either SQL techs or MongoDB work – Sammaye Oct 11 '16 at 11:12
18

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 }
000
  • 26,951
  • 10
  • 71
  • 101
1

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

matthewtole
  • 3,207
  • 22
  • 19