MongoDB versus MySQL Document Store Command Comparisons III

This time we will look at the differences in updating records between MongoDB and the MySQL Document Store.  Syntactically they are pretty different.  I am still following the Getting Started With MongoDB article for example queries.

Updating Records

In Mongo we update thusly:
> db.restaurants.update(
... { "name" : "Juni" },
... {
...  $set: { "cuisine" : "American (new)" },
...  $currentDate: { "lastModified" : true }
... }
... )
WriteResult({ "nMatched" : 1, "nUpserted" : 0, "nModified" : 1 })
>

The same update in the MySQL Document Store can be a lot different.  We could update using SQL or NoSQL.  I would like to update the document with the change to the cuisine and set the last modified to the current data.  The first change is pretty simple:

db.restaurants.modify("name = 'Juni'").set("cuisine","American (new)")



 But what about that last modified value? Well, that on the SQL side would the values of now() but the MySQL NoSQL side does not have that concept, at least it is not documented.  And lastModified is not in the document itself?!?!  Is it metadata hiding somewhere and hiding somewhere in the Mongo documentation??

Well, after some frustration with searching for document metadata and timestamps with Mongo documents, I decided to circle back to this later.

Updating Embedded Fields

The updating of embedding fields can be as mess but both products can handle  this operation.  The Mongo version is:

> db.restaurants.update(
... { "restaurant_id" : "41156888" },
... { $set: { "address.street": "East 31st Street" }}
... )
And the MySQL Document Store's version:

db.restaurants.modify("restaurant_id ='41156888'").set("address.street","East 31st Street")

So both do what is wanted but have much different syntax.

Updating Multiple Documents

Changing multiple records can be tricky, especially for novices or those learning new software.  Usually it is best to run the SQL version of the old  MySQL shell in --i-am-a-dummy mode for novices where forgetting a WHERE clause can be disastrous.  But the new MySQL shell does not have this option and Mongo forces you to expressly state you want to change multiple records.  Here is a distinction between the two products:


> db.restaurants.update( 
   { "address.zipcode" : "10016", "cuisine" : "Other"}, 
   { $set: { "cuisine" : "Category to be determined"}},
   { multi: true }
 )
WriteResult({ "nMatched" : 20, "nUpserted" : 0, "nModified" : 20 })
> 

Mongo will update only one record unless multi is set to true.  If not set you will get only one record updated. 

MySQL has no such limitation and will change multiple records. 

db.restaurants.modify('address.zipcode = "10016" and cuisine = "Other"').set('cuisine','TBD')
Query OK, 20 items affected (0.2997 sec)

I am also a fan of the explicit and in the MySQL query and not much of a fan of the implied and in the Mongo query. Why? When you try to debug things at two in the morning it is very easy to assume an or or other comparison operator.  When you program assembler you get picky about things like or, xor, and ands.

Picking Output Keys

You may not want all the keys and values from a document every time you dive into the data. Specifying specific keys again shows the differences in syntax between the two products

Mongo:
 > db.restaurants.find( { "name" : "Pizza Plus" }, 
   { name : 1 , borough : 1})
{ "_id" : ObjectId("5b2293b4f46382c40db8264f"), "borough" : "Brooklyn", "name" : "Pizza Plus" }
{ "_id" : ObjectId("5b2293b6f46382c40db86fb0"), "borough" : "Manhattan", "name" : "Pizza Plus" }
{ "_id" : ObjectId("5b2293b6f46382c40db8854b"), "borough" : "Brooklyn", "name" : "Pizza Plus" }
>

You get the _id if you want it or not. MySQL only gives you the desired values for the specified keys.

MySQL:

db.restaurants.find('name = "Pizza Plus"').fields('name','borough')
[
    {
        "borough": "Brooklyn",
        "name": "Pizza Plus"
    },
    {
        "borough": "Manhattan",
        "name": "Pizza Plus"
    },
    {
        "borough": "Brooklyn",
        "name": "Pizza Plus"
    }
]
3 documents in set (0.0486 sec)


So those extra characters are not so bothersome with small data sets but when you have millions of lines or more that extra really add overhead.

 

MongoDB versus MySQL Document Store command comparisons I

Both MongoDB and the MySQL Document Store are JSON document stores.  The syntax differences in the two products are very interesting.  This long will be a comparison of how commands differ between these two products and may evolve into a 'cheat sheet' if there is demand.

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

 I have widows with both shells ready to go and not it is time to start the comparison.

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" }

Porting Data From MongoDB to MySQL Document Store in TWO Easy Steps

Porting data from MongoDB to the MySQL Document Store is very easy.  The example I will use is an example data set from the good folks at Mongo named zips.json that contains a list of US Postal Codes and can be found at http://media.mongodb.org/zips.json for your downloading pleasure.

I copied the file into the Downloads directory on my Unbuntu laptop and then fired up the new MySQL Shell.  After login, I created a new schema creatively named zips with session.createSchema('zips').  When then set the db object to this new schema with the command \use zips.

Creating a new schema named 'zips' and then informing the system that I wish to use this new schema as the db object

Now it is time to populate the schema with a collection for holding documents. The collection is named zip and is created with db.createCollection('zip') The next step is to read the zips.json file into the collection using Python

We need to create a new collection named zip in the schema we just created and then switch to Python mode to read in the data line by line and store it as a document in the zip collection.

You might want to go back and read the wonderful presentation and scripts by Giuseppe Maxia on loading MongoDB data into MySQL at https://github.com/datacharmer/mysql-document-store as he originated this very useful bit of code.

One thing that helps is that this data set has a _id field that the MySQL Document Store will grab and use as the InnoDB primary key.  I will have to brush up on my Python to extract another value from data sets to use for the _id field.


And we can now perform NoSQL searches on the data.

A NoSQL search of the data in the zip collection

Or SQL. First we have to change to SQL node with \sql and then search for the same record with SELECT * FROM zip WHERE _id='01010';

A SQL search of the data in the zip table

If you have questions about porting data from MongoDB into the MySQL Document Store or the Document Store in general, please drop me a line.