I found an excellent Mongo tutorial Getting Started With MongoDB that I use as a framework to explore these two JSON document stores.
The Data
I am using the primer-dataset.json file that MongoDB has been using for years in their documentation, classes, and examples. MySQL has created the world_x data set based on the world database used for years in documentation, classes and examples. The data set is a collection of JSON documents filled with restaurants around Manhattan.For the Mongo examples the schema name is test and the collection is named restaurants while the MySQL corollary schema name is nyeats and the collection is named restaurants. I kept the collection names the same between the two products and hope that the differences in schema names causes no problems. Please see my previous entry if you seek details on loading this data into the MySQL Document Store.
Starting the Shells
The first step in comparing how the two work is access the data through their respective shells. |
| The MySQL mysqlsh connected to the nyeats schema |
 |
| The MongoDB mongo shell connected to the test schema |
All The Records in a Collection
Both use db as a global variable to point to the current schema. Simply typing db at the command prompt will report back the current active schema for both.
But what if you want to see all the records in the collection restaurants? With both you can issue db.restaurants.find() but where MySQL returns all the documents in the collection Mongo has a pager that requires you to type 'it' to continue?
Find Documents by Cuisine
So lets pick restaurants by their cuisine and since Red Beans and Rice is one of my favorites we will use Cajun as the cuisine of choice. The arguments to the find() function are a JSON object in Mongo and an equation for MySQL.MySQL: db.restaurants.find("cuisine = 'Cajun'")
Mongo: db.restaurants.find( { "cuisine" : "Cajun" })
The output is show below under 'Output From Cajun Cuisine as it takes up a lot of real estate on a computer screen. The big difference for those who do not want to page down is that MySQL pretty prints the output while Mongo does not. The pretty print is much easier on my old eyes.
Restaurants By Zipcode
How about we look for restaurants in one Zipcode (or postal code for those outside the USA). By the way a Zipcode can cover a lot of territory.Mongo takes a JSON object as the search parameter while MySQL wants and equation. Note that we are using a second tier key 'address.zipcode' to reach the desired information.
MySQL: db.restaurants.find("address.zipcode = '10075'")
MongoDB: db.restaurants.find( { "address.zipcode": "10075" })
When gt Is Not Great Than >!!!
I wanted to tinker with the above by changing the equal sign to a great than. It is easy to change the equal sign in the MySQL argument to any other relation symbol like <, >, or >= intuitively. I am still working on getting Mongo's $gt to work (Not intuitive or easy).
Logical OR
So far there has not been a whole lot of difference between the two. But now we start to see differences. The or operator for Mongo wants a JSON array with the delimiters inside JSON objects. MySQL looks more like traditional SQL.
MongoDB: db.restaurants.find(
{ $or : [ { "cuisine": "Cajun"}, { "address.zipcode": "10075" } ] } )
MySQL: db.restaurants.find(
"cuisine = 'Cajun' OR address.zipcode = '10075'")
To me the MySQL argument looks more like every other programming language I am used to.
Sorting on Two Keys
Let sort the restaurants by burough and zipcode, both ascending. Mongo is looking for JSON objects with the key name and sort order (1 for ascending, -1 for descending!) while MySQL defaults to ascending on the keys provided.MongoDB: db.restaurants.find().sort( { "burough" : 1, "address.zipcode" : 1 })
MySQL: db.restaurants.find().sort("burough","address.zipcode")
End of Part I
I am going to spend some time to dive deeper into the differences between the two and especially Mongo's confusing (at least to me) great than expression.
Output From Cajun Cuisine
MySQL:JS > db.restaurants.find("cuisine = 'Cajun'")
[
{
"_id": "00005b2176ae00000000000010ec",
"address": {
"building": "1072",
"coord": [
-74.0683798,
40.6168076
],
"street": "Bay Street",
"zipcode": "10305"
},
"borough": "Staten Island",
"cuisine": "Cajun",
"grades": [
{
"date": {
"$date": 1408579200000
},
"grade": "A",
"score": 13
},
{
"date": {
"$date": 1391644800000
},
"grade": "A",
"score": 9
},
{
"date": {
"$date": 1375142400000
},
"grade": "A",
"score": 12
},
{
"date": {
"$date": 1338336000000
},
"grade": "A",
"score": 8
}
],
"name": "Bayou",
"restaurant_id": "40974392"
},
{
"_id": "00005b2176ae000000000000128a",
"address": {
"building": "9015",
"coord": [
-73.8706606,
40.7342757
],
"street": "Queens Boulevard",
"zipcode": "11373"
},
"borough": "Queens",
"cuisine": "Cajun",
"grades": [
{
"date": {
"$date": 1420848000000
},
"grade": "A",
"score": 11
},
{
"date": {
"$date": 1400457600000
},
"grade": "A",
"score": 7
},
{
"date": {
"$date": 1384473600000
},
"grade": "A",
"score": 12
},
{
"date": {
"$date": 1370390400000
},
"grade": "B",
"score": 16
},
{
"date": {
"$date": 1338249600000
},
"grade": "A",
"score": 7
}
],
"name": "Big Easy Cajun",
"restaurant_id": "41017839"
},
{
"_id": "00005b2176ae0000000000002146",
"address": {
"building": "90-40",
"coord": [
-73.7997187,
40.7042655
],
"street": "160 Street",
"zipcode": "11432"
},
"borough": "Queens",
"cuisine": "Cajun",
"grades": [
{
"date": {
"$date": 1416873600000
},
"grade": "A",
"score": 9
},
{
"date": {
"$date": 1384732800000
},
"grade": "A",
"score": 10
},
{
"date": {
"$date": 1366070400000
},
"grade": "B",
"score": 16
},
{
"date": {
"$date": 1345507200000
},
"grade": "B",
"score": 18
}
],
"name": "G & L Cajun Grill",
"restaurant_id": "41336510"
},
{
"_id": "00005b2176ae0000000000002ce7",
"address": {
"building": "2655",
"coord": [
-74.1660553,
40.5823983
],
"street": "Richmond Avenue",
"zipcode": "10314"
},
"borough": "Staten Island",
"cuisine": "Cajun",
"grades": [
{
"date": {
"$date": 1412035200000
},
"grade": "A",
"score": 10
},
{
"date": {
"$date": 1392768000000
},
"grade": "B",
"score": 18
},
{
"date": {
"$date": 1371772800000
},
"grade": "B",
"score": 16
},
{
"date": {
"$date": 1335916800000
},
"grade": "A",
"score": 11
},
{
"date": {
"$date": 1322611200000
},
"grade": "A",
"score": 11
}
],
"name": "Cajun Cafe & Grill",
"restaurant_id": "41485811"
},
{
"_id": "00005b2176ae000000000000352d",
"address": {
"building": "509",
"coord": [
-73.964513,
40.693846
],
"street": "Myrtle Avenue",
"zipcode": "11205"
},
"borough": "Brooklyn",
"cuisine": "Cajun",
"grades": [
{
"date": {
"$date": 1417651200000
},
"grade": "A",
"score": 13
},
{
"date": {
"$date": 1386028800000
},
"grade": "A",
"score": 9
},
{
"date": {
"$date": 1370390400000
},
"grade": "A",
"score": 4
},
{
"date": {
"$date": 1355529600000
},
"grade": "A",
"score": 13
}
],
"name": "Soco Restaurant",
"restaurant_id": "41585575"
},
{
"_id": "00005b2176ae0000000000003579",
"address": {
"building": "36-18",
"coord": [
-73.916912,
40.764514
],
"street": "30 Avenue",
"zipcode": "11103"
},
"borough": "Queens",
"cuisine": "Cajun",
"grades": [
{
"date": {
"$date": 1418256000000
},
"grade": "A",
"score": 10
},
{
"date": {
"$date": 1394668800000
},
"grade": "A",
"score": 0
},
{
"date": {
"$date": 1375488000000
},
"grade": "B",
"score": 17
},
{
"date": {
"$date": 1358467200000
},
"grade": "A",
"score": 10
},
{
"date": {
"$date": 1341446400000
},
"grade": "A",
"score": 12
},
{
"date": {
"$date": 1324080000000
},
"grade": "A",
"score": 10
}
],
"name": "Sugar Freak",
"restaurant_id": "41589054"
},
{
"_id": "00005b2176ae0000000000004172",
"address": {
"building": "1433",
"coord": [
-73.9535815,
40.6741202
],
"street": "Bedford Avenue",
"zipcode": "11216"
},
"borough": "Brooklyn",
"cuisine": "Cajun",
"grades": [
{
"date": {
"$date": 1397001600000
},
"grade": "A",
"score": 8
},
{
"date": {
"$date": 1365033600000
},
"grade": "A",
"score": 10
}
],
"name": "Catfish",
"restaurant_id": "41685267"
}
]
7 documents in set (0.0488 sec)
Mongo:
db.restaurants.find( { "cuisine" : "Cajun" })
{ "_id" : ObjectId("5b2293b5f46382c40db834ce"), "address" : { "building" : "1072", "coord" : [ -74.0683798, 40.6168076 ], "street" : "Bay Street", "zipcode" : "10305" }, "borough" : "Staten Island", "cuisine" : "Cajun", "grades" : [ { "date" : ISODate("2014-08-21T00:00:00Z"), "grade" : "A", "score" : 13 }, { "date" : ISODate("2014-02-06T00:00:00Z"), "grade" : "A", "score" : 9 }, { "date" : ISODate("2013-07-30T00:00:00Z"), "grade" : "A", "score" : 12 }, { "date" : ISODate("2012-05-30T00:00:00Z"), "grade" : "A", "score" : 8 } ], "name" : "Bayou", "restaurant_id" : "40974392" }
{ "_id" : ObjectId("5b2293b5f46382c40db8366b"), "address" : { "building" : "9015", "coord" : [ -73.8706606, 40.7342757 ], "street" : "Queens Boulevard", "zipcode" : "11373" }, "borough" : "Queens", "cuisine" : "Cajun", "grades" : [ { "date" : ISODate("2015-01-10T00:00:00Z"), "grade" : "A", "score" : 11 }, { "date" : ISODate("2014-05-19T00:00:00Z"), "grade" : "A", "score" : 7 }, { "date" : ISODate("2013-11-15T00:00:00Z"), "grade" : "A", "score" : 12 }, { "date" : ISODate("2013-06-05T00:00:00Z"), "grade" : "B", "score" : 16 }, { "date" : ISODate("2012-05-29T00:00:00Z"), "grade" : "A", "score" : 7 } ], "name" : "Big Easy Cajun", "restaurant_id" : "41017839" }
{ "_id" : ObjectId("5b2293b5f46382c40db84528"), "address" : { "building" : "90-40", "coord" : [ -73.7997187, 40.7042655 ], "street" : "160 Street", "zipcode" : "11432" }, "borough" : "Queens", "cuisine" : "Cajun", "grades" : [ { "date" : ISODate("2014-11-25T00:00:00Z"), "grade" : "A", "score" : 9 }, { "date" : ISODate("2013-11-18T00:00:00Z"), "grade" : "A", "score" : 10 }, { "date" : ISODate("2013-04-16T00:00:00Z"), "grade" : "B", "score" : 16 }, { "date" : ISODate("2012-08-21T00:00:00Z"), "grade" : "B", "score" : 18 } ], "name" : "G & L Cajun Grill", "restaurant_id" : "41336510" }
{ "_id" : ObjectId("5b2293b5f46382c40db850c6"), "address" : { "building" : "2655", "coord" : [ -74.1660553, 40.5823983 ], "street" : "Richmond Avenue", "zipcode" : "10314" }, "borough" : "Staten Island", "cuisine" : "Cajun", "grades" : [ { "date" : ISODate("2014-09-30T00:00:00Z"), "grade" : "A", "score" : 10 }, { "date" : ISODate("2014-02-19T00:00:00Z"), "grade" : "B", "score" : 18 }, { "date" : ISODate("2013-06-21T00:00:00Z"), "grade" : "B", "score" : 16 }, { "date" : ISODate("2012-05-02T00:00:00Z"), "grade" : "A", "score" : 11 }, { "date" : ISODate("2011-11-30T00:00:00Z"), "grade" : "A", "score" : 11 } ], "name" : "Cajun Cafe & Grill", "restaurant_id" : "41485811" }
{ "_id" : ObjectId("5b2293b5f46382c40db8590d"), "address" : { "building" : "509", "coord" : [ -73.964513, 40.693846 ], "street" : "Myrtle Avenue", "zipcode" : "11205" }, "borough" : "Brooklyn", "cuisine" : "Cajun", "grades" : [ { "date" : ISODate("2014-12-04T00:00:00Z"), "grade" : "A", "score" : 13 }, { "date" : ISODate("2013-12-03T00:00:00Z"), "grade" : "A", "score" : 9 }, { "date" : ISODate("2013-06-05T00:00:00Z"), "grade" : "A", "score" : 4 }, { "date" : ISODate("2012-12-15T00:00:00Z"), "grade" : "A", "score" : 13 } ], "name" : "Soco Restaurant", "restaurant_id" : "41585575" }
{ "_id" : ObjectId("5b2293b5f46382c40db8596b"), "address" : { "building" : "36-18", "coord" : [ -73.916912, 40.764514 ], "street" : "30 Avenue", "zipcode" : "11103" }, "borough" : "Queens", "cuisine" : "Cajun", "grades" : [ { "date" : ISODate("2014-12-11T00:00:00Z"), "grade" : "A", "score" : 10 }, { "date" : ISODate("2014-03-13T00:00:00Z"), "grade" : "A", "score" : 0 }, { "date" : ISODate("2013-08-03T00:00:00Z"), "grade" : "B", "score" : 17 }, { "date" : ISODate("2013-01-18T00:00:00Z"), "grade" : "A", "score" : 10 }, { "date" : ISODate("2012-07-05T00:00:00Z"), "grade" : "A", "score" : 12 }, { "date" : ISODate("2011-12-17T00:00:00Z"), "grade" : "A", "score" : 10 } ], "name" : "Sugar Freak", "restaurant_id" : "41589054" }
{ "_id" : ObjectId("5b2293b6f46382c40db86551"), "address" : { "building" : "1433", "coord" : [ -73.9535815, 40.6741202 ], "street" : "Bedford Avenue", "zipcode" : "11216" }, "borough" : "Brooklyn", "cuisine" : "Cajun", "grades" : [ { "date" : ISODate("2014-04-09T00:00:00Z"), "grade" : "A", "score" : 8 }, { "date" : ISODate("2013-04-04T00:00:00Z"), "grade" : "A", "score" : 10 } ], "name" : "Catfish", "restaurant_id" : "41685267" }
