MongoDB: … $where a < b

$where allows to expand the expressiveness of MongoDB queries giving programmers an important choice when querying MongoDB.

Use Case

The use case for this blog is to find all documents where the value of property “b” is larger than the value of property “a”. The data set is

> use where
switched to db where
> db.whereColl.save({"a":3, "b":5})
> db.whereColl.save({"a":4, "b":5})
> db.whereColl.save({"a":5, "b":5})
>

Let’s try a simple query first (real values replaced by “…” used to save space):

> db.whereColl.find({"b": {$gt : 4}})
{ "_id" : ObjectId("..."), "a" : 3, "b" : 5 }
{ "_id" : ObjectId("..."), "a" : 4, "b" : 5 }
{ "_id" : ObjectId("..."), "a" : 5, "b" : 5 }
>

This query works quite nicely when asking for all document where “b” is greater than 4. Let’s try to now run the query we set out from:

> db.whereColl.find({"b": {$gt : "a"}})
>

This query does not return a result. Most likely because property “b” contains numbers and “a” is a string constant. Let’s try a different approach:

> db.whereColl.find({"b": {$gt : a}})
Tue Dec 14 17:59:33 ReferenceError: a is not defined (shell):1
>

This query does not return the desired result, either. The reason is that “a” as a variable is not defined.

$where

Enter $where. $where gives the ability to ask the database to execute a Javascript function (or expression) during query processing. Let’s try this query:

> db.whereColl.find({$where: "this.b > this.a"})
{ "_id" : ObjectId("..."), "a" : 3, "b" : 5 }
{ "_id" : ObjectId("..."), "a" : 4, "b" : 5 }
>

This query works. “this” points to the current document at evaluation time and supports the access to properties in the document. This example shows that $where allows expressions to be evaluated by the database that cannot be formulated using the regular query approach of MongoDB.

Discussion

Leaving expressiveness aside, the MongoDB documentation gives variations on the $where as well as performance hints: http://www.mongodb.org/display/DOCS/Server-side+Code+Execution#Server-sideCodeExecution-{{%24where}}ClausesandFunctionsinQueries.

With the strong warning for the use of $where, when does it make sense to use? First, performance measurement with and without the use of $where is a good strategy because if the database does not execute the condition in $where, the application code has to do it. There might be cases where the database is faster.

From an application design perspective, it is always possible to use $where in order to get the queries correct. And later, optimize by removing the $where and moving the expression into the application programs if the $where turns out to be slower. So there is an advantage from a design perspective for sure.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s