MongoDb $and operator with geospatial queries

java, mongodb

Solution

Is $and ever necessary, and if so, in what sorts of cases?

Here is the classic SQL

SELECT * FROM Table
WHERE (x = 1 OR y = 0)
AND   (w = 1 OR z = 0)

Prior to the `$and` operator, this was very difficult to do. You could not do the following:

db.Table.find({$or: [{x:1},{y:0}] , $or: [{w:1},{z:0}]})

The query "looks" correct, but it is invalid JSON. You'll notice how I have two top-level `$or` keys. That comma is supposed to represent the "and", but it doesn't work in this case.

The `$and` operator is designed to solve this problem.

Does $and differ in some fundamental way from the implicit representation with respect to indexes?

It should compile to the same basic query and use the same indexes. If not, this is definitely a bug.

Why exactly does $and not work with geospatial queries?

The behaviour you are seeing is a probably a bug and should be reported: http://jira.mongodb.org/

Also, please not that MongoDB has special limits on Geospatial indexes. Your query may not be using indexing in an ideal fashion.

Problem

I'm working on a system in Java, part of which renders expressions from an internal representation to a MongoDb query. I render "AND" expressions the same way I render "OR" expressions. So if I have an expression like "category='GIBBLY' AND active=true", it would be rendered as: `{$and:[{"category":"GIBBLY"},{"active":true}]}` I understand that this isn't strictly necessary in this case. What I can't figure out is whether there is ever a case where $and is necessary (in my system, there will often be nested ANDs and ORs, if that makes any difference). The reason this is an issue for me is that the $and operator seems to fail for geospatial queries. A query like this: `{"$and":[{"loc":{"$nearSphere":[-79.75 , 43.5], "$maxDistance":0.00156787}}, {"active":true}]}` fails with a "can't find special index: 2d" error. If I re-do the query as: `{"loc":{"$nearSphere":[-79.75 , 43.5], "$maxDistance":0.00156787}, "active":true}` it works fine. I tried to do some research, and found a discussion of the issue on the mongodb-user group, which referred to a discussion of compound indexes in geospatial queries. None of the information I found really explains why exactly $and doesn't work with the geospatial query. I have no compound indexes defined at all in my database, and the non-$and query works, so I'm not sure how $and works differently with respect to indexes. To summarize, my questions are: Is $and ever necessary, and if so, in what sorts of cases? Does $and differ in some fundamental way from the implicit representation with respect to indexes? Why exactly does $and not work with geospatial queries?

Original source