Using OR in your queries makes kittens cry.

[Randy]

-- Query, OR, and you.

I am investigating MongoDb, because 1) it's cool, 2) I'm sick of SQL,
and 3) I'm tired of schemas.

Why is there a limitation on using OR type queries? OR type queries
would be really nice and enable some complicated queries while using
the easy to use JSON-style query format.

I want to be able to query "a == 1 or b == 2" in my collections. And
it would be nice if I could hit the indexes. It would be double nice
if I could use the hash style queries.

I understand that you can always use query directly on the server by
passing in javascript via $where. But it doesn't use the indexes.

What if you could pass in multiple hashes to the find() method and
those would be an OR condition.

Here's my snippet from the mongodb JS console:

> db.test.find()
{"_id" : "4a9580743b19b3097669443b" , "a" : 1}
{"_id" : "4a9580823b19b3097669443c" , "b" : 2}
{"_id" : "4a9580993b19b3097669443d" , "b" : 2}
{"_id" : "4a95809f3b19b3097669443e" , "b" : 2}
{"_id" : "4a9580bc3b19b3097669443f" , "b" : 2}
{"_id" : "4a9580c03b19b30976694440" , "c" : 3}
> db.test.count({a:1})
1
> db.test.count({b:2})
4
> db.test.count({b:2},{a:1}) # <=== Hypothetical OR query. this.b == 2 or this.a ==1. Doesn't work. :()
4
> db.test.count("this.b == 2 || this.a == 1")
5
> db.test.count({a:1, b:2}) # AND query
0


Here's a snippet from my IRB console using the Ruby driver. This
shows that the $where clause type OR queries are not using the index.

# Use the cool hash style and use the indexes!
irb(main):299:0> foo.find('a' => 1).explain
=> {"cursor"=>"BtreeCursor a_1", "startKey"=>{"a"=>1}, "endKey"=>
{"a"=>1}, "nscanned"=>1.0, "n"=>1, "millis"=>0, "allPlans"=>
[{"cursor"=>"BtreeCursor a_1", "startKey"=>{"a"=>1}, "endKey"=>
{"a"=>1}}]}

# Use the $where style and but doesn't use the indexes
irb(main):300:0> foo.find('$where' => 'this.a == 1').explain
=> {"cursor"=>"BasicCursor", "startKey"=>{}, "endKey"=>{},
"nscanned"=>3.0, "n"=>1, "millis"=>20, "allPlans"=>
[{"cursor"=>"BasicCursor", "startKey"=>{}, "endKey"=>{}}]}

[Eliot Horowitz]

ORs would be good.
We're planning on doing them.
You can track here: http://jira.mongodb.org/browse/SERVER-205
Turns out its not a very often asked for feature (no votes as of yet), but we agree its important.

[Michael Dirolf]

You can track progress on $or, which will allow queries like this, here:
http://jira.mongodb.org/browse/SERVER-205

Another good reference for doing more simple or type queries is here:
http://www.mongodb.org/display/DOCS/OR+operations+in+query+expressions

[Randy]

I would like to change the subject of this post from "Using OR in your
queries makes kittens cry" to "You guys are awesome and make me happy"

Thanks for the quick reply and the heads up.

> You can track progress on $or, which will allow queries like this, here:http://jira.mongodb.org/browse/SERVER-205
>

> Another good reference for doing more simpleortype queries is here:http://www.mongodb.org/display/DOCS/OR+operations+in+query+expressions

>
> On Aug 26, 2009, at 3:09 PM, Randy wrote:
>
>
>

> > -- Query,OR, and you.

>
> > I am investigating MongoDb, because 1) it's cool, 2) I'm sick of SQL,
> > and 3) I'm tired of schemas.
>

> > Why is there a limitation on usingORtype queries?  ORtype queries

> > would be really nice and enable some complicated queries while using
> > the easy to use JSON-style query format.
>

> > I want to be able to query "a == 1orb == 2" in my collections.  And

> > it would be nice if I could hit the indexes.  It would be double nice
> > if I could use the hash style queries.
>
> > I understand that you can always use query directly on the server by
> > passing in javascript via $where.  But it doesn't use the indexes.
>
> > What if you could pass in multiple hashes to the find() method and
> > those would be anORcondition.
>
> > Here's my snippet from the mongodb JS console:
>
> >> db.test.find()
> > {"_id" : "4a9580743b19b3097669443b" , "a" : 1}
> > {"_id" : "4a9580823b19b3097669443c" , "b" : 2}
> > {"_id" : "4a9580993b19b3097669443d" , "b" : 2}
> > {"_id" : "4a95809f3b19b3097669443e" , "b" : 2}
> > {"_id" : "4a9580bc3b19b3097669443f" , "b" : 2}
> > {"_id" : "4a9580c03b19b30976694440" , "c" : 3}
> >> db.test.count({a:1})
> > 1
> >> db.test.count({b:2})
> > 4
> >> db.test.count({b:2},{a:1})   # <=== HypotheticalORquery.  this.b  

> >> == 2orthis.a ==1.  Doesn't work. :()

> > 4
> >> db.test.count("this.b == 2 || this.a == 1")
> > 5
> >> db.test.count({a:1, b:2})    # AND query
> > 0
>
> > Here's a snippet from my IRB console using the Ruby driver.  This

> > shows that the $where clause typeORqueries are not using the index.

[Daniel Friesen]

That's probably because of $in. People are probably working around $or
by modeling their data in a way they can easily use an $in on an array
instead.

~Daniel Friesen (Dantman, Nadir-Seen-Fire) [http://daniel.friesen.name]

[Dwight Merriman]

We want to do it. It's just a SMOP and unfortunately the implementation
is more than a little work because there is query optimizer code to
write, not just expression evaluation, to make it happen.

Thanks