Comparing NumberLongs (integers) via $where

[Eric Naeseth]

Hello mongodb-user readers,

Recently I needed to write a query to find all documents where two
properties were equal to each other. We'll just call them `a` and `b`
for purposes of this post.

Naively, I ran the following query:

db.myCollection.find({$where: 'this.a == this.b'})

To my surprise and confusion, the query returned *some* of the results
that should have matched, but not all of them. Debugging this took
some time, so I'm posting here for the benefit of the community, and
to see if there's a better way to accomplish my query than the
workaround I used.

The collection I'm querying has poor data type consistency on the `a`
property. Some of the documents, when viewed through the MongoDB
shell, look like this:

{ "a" : NumberLong(1234), "b" : NumberLong(1234) }

In these documents, both of the values were clearly passed to Mongo as
integers, and Mongo is wrapping them in NumberLong objects to prevent
their values from losing precision in the JavaScript environment.

The query I wrote fails to match these documents because NumberLong
values (apparently) cannot be compared using ==:

> new NumberLong(1234) == new NumberLong(1234)
false

So, I wasn't finding any of the documents were both `a` and `b` were
the correct data types. This is quite frustrating, but I did find a
workaround. NumberLong objects do have a useful string representation:

> String(new NumberLong(1234))
"NumberLong(1234)"

Since strings can obviously be compared using ==, I rewrote my query:

db.myCollection.find({$where: 'String(this.a) == String(this.b)'})

This successfully found the documents that I had been excluding before
-- but, unfortunately, it excluded all the documents I *had* been
finding previously! Because of the aforementioned poor data type
consistency, some of the documents in my collection look like this:

{ "a" : 456, "b" : new NumberLong(456) }

These "incorrectly-typed" documents were indeed found by the old
query, because for some reason beyond my understanding, you can
compare a number and a NumberLong using ==:

> 12 == new NumberLong(12)
true

So, finally, I ended up using the following query:

db.myCollection.find({$where: 'this.a == this.b || String(this.a)
== String(this.b)'})

This finds all the documents I need. Success!

But I feel that there has to be a better way. Of course these issues
don't arise using "normal" queries (i.e., not using $where) because
Mongo itself has a native representation for integer values, thanks to
them being a first-class data type in BSON.

Is there any more elegant way to work around this problem?

Thanks,
Eric Naeseth

[Alvin Richards]

So how $where is processed is documented here

http://www.mongodb.org/display/DOCS/Server-side+Code+Execution

When a $where is processed, the JavaScript is evaluated server side
for each of the documents. The built in operators (e.g. $or, $in etc.)
will be BSON datatype aware as you point out. In this case, the BSON
type is getting converted to the closest JavaScript type for the
evaluation of your expression. The problem is inconsistent datatypes.

Assuming that you can't force the datatypes to be consistent during
the insert you solutions seems to be the obvious one. However, I need
to dig further to understand why the 'String(this.a) ==
String(this.b)' did not return all the documents.

-Alvin

[Eric Naeseth]

Hi Alvin,

Thanks for the reply!

On Thu, Dec 9, 2010 at 9:30 PM, Alvin Richards <al...@10gen.com> wrote:
> So how $where is processed is documented here
>
> http://www.mongodb.org/display/DOCS/Server-side+Code+Execution
>
> When a $where is processed, the JavaScript is evaluated server side
> for each of the documents. The built in operators (e.g. $or, $in etc.)
> will be BSON datatype aware as you point out. In this case, the BSON
> type is getting converted to the closest JavaScript type for the
> evaluation of your expression. The problem is inconsistent datatypes.

Actually, as far as I can tell, the inconsistent datatypes saved me
from getting back no results at all -- although perhaps that would
have been easier to debug.

The problem is the non-obvious behavior of == in Mongo JavaScript
expressions. I would expect a comparison of the same value of the same
type, NumberLong(1) == NumberLong(1), to be true, when in fact it is
false. I would also expect a comparison of the same value of different
types to perhaps be false, even using == instead of ===, but in fact,
1 == NumberLong(1) is true.

> Assuming that you can't force the datatypes to be consistent during
> the insert you solutions seems to be the obvious one. However, I need
> to dig further to understand why the 'String(this.a) ==
> String(this.b)' did not return all the documents.
>
> -Alvin

String(NumberLong(1)) != String(1) because "NumberLong(1)" != "1".

Certainly, I should be ensuring that the correct datatypes are stored
in my database. Using both types of comparison was simply a quick fix,
and I can drop the 'this.a == this.b' branch when I perform the cleanup.

-Eric

> --
> You received this message because you are subscribed to the Google Groups "mongodb-user" group.
> To post to this group, send email to mongod...@googlegroups.com.
> To unsubscribe from this group, send email to mongodb-user...@googlegroups.com.
> For more options, visit this group at http://groups.google.com/group/mongodb-user?hl=en.
>
>