MonoDB versus MySQL Document Store Command Comparisons II

Last time I was stumped by the MongoDB $gt: operator.  I wanted to look for restaurants in a certain Manhattan burough OR in a zipcode greater than a certain zipcode.  Well, I was getting different results between Mongo and MySQL.

To > or Not To >, That Is the Query

Lets say we have three records with the same key but the values are 1, 2, and "3". Yup, you got it two numerics and one string.  I would expect schema less data to be free flowing, not typed, and pretty much a free for all.  Whoops. Bad assumption on my part for Mongo use.

I added three JSON documents into Mongo as can be seen below:

Our three documents with the values of 1, 2, & "3" in Mongo

And the same data into the MySQL Document Store:

Our test data in the MySQL Document Store with the values of 1, 2, and "3"

The Search

 Now search for items with a value greater than 1. MySQL handles this by returning two records:

MySQL says there are two documents where the value of item is greater than 1

However Mongo differs:

Mongo says ther is only one document with the value of item great than 1

What Happened?

Mongo's reference manual under db.collection.find informs us that 'comparison operators only perform comparisons on documents where the BSON type of the target field matches the type of the query operand.' and silly me was trying to compare a numeric to a string.

Now you can enforce data types in a Mongo collection by using schema validation.  But I thought the beauty of schema less data was that you did not have to normalize the data.

Am I picking nits? Well, I have been pushing data around in computers for close to four decades now and wish I had a nickel (or other small coin of similar value in another curency) for every time I had to 'wash' my data to switch it from Type X to Type Y or reshuffle dates or even add/remove thousands separators.  To me schema less means that "3" and 3 are equal. At least until they are cast to a type.

Next Time

I have been getting some very good feedback on this subject and will delve into this more.