Converting Nested MongoDB/BSON/JSON to MySQL/RDBMS

The Source: Business Service Providers

It is an impressive feat for MongoSluice to be able to produce multiple tables that are linked together by foreign and primary keys.  But can MongoSluice perform the same when data begins to get more complex?  The answer is YES!  Recently, we found a JSON file that was composed of an API call to US gov data.  The file was not chosen for its size, but its complexity (4 layers deep).  We have company info embedded in properties, embedded in provider, embedded in definitions, all embedded in the root object, businessServiceProviders.

A link to the file can be found here in JSON format.  The API call can be found here.  Check out the Mongo Docs to see how to import this file into MongoDB for easy viewing.

The Data: (Just a Little Taste)

“definitions” : {

“Provider” : {

“properties” : {

“ita_contact_email” : {

“type” : “string”,

“description” : “Email for ITA contact.”

},

“company_name” : {

“type” : “string”,

“description” : “Name of company providing the service.”

},

“company_phone” : {

“type” : “string”,

“description” : “Phone number for company.”

},

“company_address” : {

“type” : “string”,

“description” : “Street, city, and country address for company.”

},

“company_website” : {

“type” : “string”,

“description” : “URL for company site.”

},

“company_description” : {

“type” : “string”,

“description” : “Description of company.”

},

“company_email” : {

“type” : “string”,

“description” : “Email for contact at company.”

},

“ita_office” : {

“type” : “string”,

“description” : “Name of ITA office that has provided company information.”

},

“contact_title” : {

“type” : “string”,

“description” : “Title of contact at company.”

},

“contact_name” : {

“type” : “string”,

“description” : “Name of contact at company.”

},

“category” : {

“type” : “string”,

“description” : “Category of services that company provides.”

}

}

}




}

 

Streaming MongoDB to MySQL

1. First, it interrogates every single document within the Mongo collection in order to build an accurate schema.  MongoSluice does not take shortcuts or samples of the data because it likes to be perfect.  Check out the –applyDeltas option in the MongoSluice docs to update existing documents and ignore duplicate documents.
2. Now that MongoSluice has achieved its most difficult task, it can quickly dump all the data to SQL.

The Results: Inexpensive Stores, Easy Analysis

Check out the tables that MongoSluice generated below.  You can see multiple children tables inside of children tables all originating from the root table businessServiceProviders.  Moving data between MongoDB and SQL is made so simple with MongoSluice allowing inexpensive stores without compromising analysis.