When to use MongoDB $and Operator

On the surface, it seems like the $and operator is useless because it is implicitly applied when MongoDB processes each expression in the query.

For instance the following query searches for all products where category=”vehicle” AND brand=”toyota”:

db.products.find({
  "category":"vehicle",
  "brand":"toyota"
})

However, consider this:

db.products.find({
  "category":"fruit",
  "category":"vehicle",
  "brand":"toyota"
})

It is very logical to think that the above query would not return anything because you are querying for documents where category=”fruit” AND category=”vehicle” AND brand=”toyota”. It turns out that the query will return the same results as the first query, the one where category=”vehicle” AND brand=”toyota”.

What is happening is that when MongoDB is processing expressions in a query it will overwrite expressions that have the same field or operator. In this case, the first expression is “category”:”fruit”. Then the second expression is “category”:”vehicle” and has the same field (“category”) as the first expression, so it will be used instead. The first expression will be completely ignored – overwritten by the second expression.

The same happens for expressions that start with an operator, for instance:

db.products.find({
  "$or":[{...}, {...}],
  "$or":[{...}, {...}]
})

The second “$or” expression will overwrite the first one and so the first $or expression will be completely ignored.

In order to avoid this implicit $and operation behaviour, you will have to use the explicit operator:

db.products.find({
  "$and":[
    {"$or":[{...}, {...}]},
    {"$or":[{...}, {...}]}
  ]
})

Leave a Reply

Your email address will not be published. Required fields are marked *

*