Moving data from MongoDB to MySQL's JSON Document Store

I had an interesting phone call from someone wanting to move from MongoDB to MySQL's new JSON Document Store. The big question was 'How do I export my Mongo data into something I can read into MySQL?"

The good news is that the folks at Mongo have given us the tools for the hard part of a simple process. For this example I am going to use some test data create previously in a database creatively named dave. The numbers are made up and I am using my name and the names of the canines residing in my home. So a quick peek at the data:

$ mongo
MongoDB shell version: 3.2.8
connecting to: test
> use dave
switched to db dave
> db.dave.find()
{ "_id" : 123, "name" : "Dave" }
{ "_id" : 456, "name" : "Jack" }
{ "_id" : 789, "name" : "Dexter" }
{ "_id" : 787, "name" : "Boo" }
> 
bye

Dumping That Data

First use mongodump -d dave to write out the data much as you would with mysqldump. Under you current working directory of your shell (assuming you are on Unix/Linux) there will be created a directory named dump. And under dump is a directory named after the example database dave.

A dave.bson file was created with the data.

BSON to Text

Executing bsondump dave.bson > output.file will convert the MongoDB BSON formatted data into something much easier to work with.

$ cat output.file 
{"_id":123.0,"name":"Dave"}
{"_id":456.0,"name":"Jack"}
{"_id":789.0,"name":"Dexter"}
{"_id":787.0,"name":"Boo"}

The output.file can be processed in a number of ways to pull it into MySQL such as using your favorite text editor to wrap insert statements around the data or using a simple program that reads a line from the text file and then send data to the database.