Skip to content
Studio 3T - The professional GUI, IDE and client for MongoDB
  • Tools
    • Aggregation Editor
    • IntelliShell
    • Visual Query Builder
    • Export Wizard
    • Import Wizard
    • Query Code
    • SQL Query
    • Connect
    • Schema Explorer
    • Compare
    • SQL ⇔ MongoDB Migration
    • Data Masking
    • Task Scheduler
    • Reschema
    • More Tools and Features
  • Solutions
  • Resources
    • Knowledge Base
    • MongoDB Tutorials & Courses
    • Tool/Feature Documentation
    • Blog
    • Community
    • Testimonials
    • Whitepapers
    • Reports
  • Contact us
    • Contact
    • Sales Support
    • Feedback and Support
    • Careers
    • About Us
  • Store
    • Buy Now
    • Preferred Resellers
    • Team Pricing
  • Download
  • My 3T
search

Studio 3T Knowledge Base

  • Documentation
  • Tutorials
  • Workshops
Take the fastest route to learning MongoDB. Cover the basics in two hours with MongoDB 101, no registration required.
Start the free course

SQL Migration: MongoDB and SQL, Coexistence Made Easy

Posted on: 13/11/2020 (last updated: 17/10/2022) by Phil Factor

Below is an excerpt from the whitepaper, SQL Migration: MongoDB and SQL, Coexistence Made Easy. Download it here.


In the past five years, the relational ‘big beasts’ such as SQL Server, MySQL, PostgreSQL, and Informix have mostly added JSON as a data transfer medium.

This doesn’t just mean that the relational database will import tables, views, or queries in JSON format, but also that it will, if necessary, accept and shred JSON as parameters to procedures and functions, and pass back results as JSON.

Document databases, such as MongoDB, CouchDB, and ElasticSearch, that use a binary extension to JSON as storage are rather different because they avoid the inherent complexity of normalized tabular storage by compromising the principle of holding data in one place only.

Where data is high in volume but ephemeral and fairly unstructured, this type of database will always be faster to ingest and store data.

For the JavaScript full-stack programmer, there is an obvious advantage in having all the layers of an application using the same language and conventions. This is a great attraction of the MEAN stack (MongoDB, Express.js, AngularJS (or Angular), and Node.js), and this sort of compatibility is the obvious choice to get a self-contained application up and running because the programmer can concentrate on the logic without having to cross a cultural border such as the object-relational divide.

As applications mature and become more essential to the organization or community that uses it, so the data has to contribute to the data flows, analysis, and reporting requirements of that organization. Increasingly, the data handlers of organizations are using different types of databases that suit the requirements, such as relational, OLAP, document, KeyValue, Column-Oriented, and graph databases, so long as they can coexist.

JSON Growth Pains

JSON was initially devised as a reaction to the intimidating complexity of XML.

At first, JSON seemed to be more of a satirical statement. It had no standard for representing some of the most essential data types such as dates, locations, byte-arrays, or BLOBs.

JSON objects are inherently unordered with no way of representing ordered attributes or lists. There was originally no standard for representing schemas or constraints.

You could use it for transferring data in the same way as you would use CSV for tables: both ends had to know the schema and constraints. There was no way of querying the contents of the document other than by rolling your own conventions using JavaScript.

Several initiatives are sorting out this problem. MongoDB uses Extended JSON in strict mode or shell mode in order to represent such datatypes as dates, binary data, timestamps, Regex expressions, IDs, long integers, and decimal numbers (see Extended JSON spec).

Whereas this is essential for unstructured data, and fine for transfer between MongoDB systems, the use of a JSON schema is much more effective for general use where the schema is pre-defined, because it also allows constraints and checks as well as specifying the data type. With JSON Schema, JSON doesn’t need all the extra datatype extensions.

There is, sadly, no defined standard for any extended JSON, and even within MongoDB, it exists in several different confusing dialects.

MongoDB’s BSON isn’t text-based at all but represents a binary parsed form of the extended JSON.

The truth is that anyone using the mongo shell or mongoimport finds no easy way of importing JSON from a ‘foreign’ (non-MongoDB) source in a way that preserves even dates as binary values rather than strings.

This is important, as you will soon find if you are expecting to be able to find documents in a collection between two dates. Studio 3T gets around this problem with SQL RDBMSs (Relational Databases) by importing directly from a relational database (MySQL, Oracle, PostgreSQL, or SQL Server) and creating the correct ‘extended JSON’ equivalent.

SQL Migration

Attempts to bridge the difference via ORM middleware have been ingenious but with rather muted success, because any attempt to represent a relational model in terms of hierarchical object models is bound to be a compromise that is difficult to optimize.

Exchange of data between heterogeneous databases via Node.js has a better chance of success, but only where relational databases can accept JSON data as a parameter as well as singleton data, and where they can make authenticated REST calls.

The implementation of microservice architectures has proved that this can be made to work successfully.

A more recent alternative approach is to use Studio 3T SQL Migration. This feature not only transfers the data in either direction but simultaneously bundles up the relevant schema change-script.

Adding a one to many relationship

When importing, it can convert the relational tables into document-based schemas, using a mapping that can be added, stored, and edited within the application. It is now able to migrate data both ways so that a collection can be scripted as a SQL table creation, followed by the insertion of data.

One can, via mappings, create a number of linked tables to represent the collection. This allows us to sidestep the use of JSON altogether as a transport medium when exporting data from MongoDB to SQL Server.


How helpful was this article?
This article was hideous
This article was bad
This article was ok
This article was good
This article was great
Thank you for your feedback!

About The Author

Phil Factor

Phil Factor (real name withheld to protect the guilty), aka Database Mole, has 30 years of experience with database-intensive applications. Despite having once been shouted at by a furious Bill Gates at an exhibition in the early 1980s, he has remained resolutely anonymous throughout his career.

Article navigation

Related articles

  • SQL Migration: MongoDB and SQL, Coexistence Made Easy
  • MongoDB Aggregation Made Easy with 3T’s Aggregation Editor
  • Moving from SQL to MongoDB? Studio 3T’s SQL Migration feature makes it simple
  • MongoDB to win further mainstream adoption with bi-directional SQL Migration release from Studio 3T
  • Incremental Execution for Export and Migration

Studio 3T

MongoDB Enterprise Certified Technology PartnerSince 2014, 3T has been helping thousands of MongoDB developers and administrators with their everyday jobs by providing the finest MongoDB tools on the market. We guarantee the best compatibility with current and legacy releases of MongoDB, continue to deliver new features with every new software release, and provide high quality support.

Find us on FacebookFind us on TwitterFind us on YouTubeFind us on LinkedIn

Education

  • Free MongoDB Tutorials
  • Connect to MongoDB
  • Connect to MongoDB Atlas
  • Import Data to MongoDB
  • Export MongoDB Data
  • Build Aggregation Queries
  • Query MongoDB with SQL
  • Migrate from SQL to MongoDB

Resources

  • Feedback and Support
  • Sales Support
  • Knowledge Base
  • FAQ
  • Reports
  • White Papers
  • Testimonials
  • Discounts

Company

  • About Us
  • Blog
  • Careers
  • Legal
  • Press
  • Privacy Policy
  • EULA

© 2023 3T Software Labs Ltd. All rights reserved.

  • Privacy Policy
  • Cookie settings
  • Impressum

We value your privacy

With your consent, we and third-party providers use cookies and similar technologies on our website to analyse your use of our site for market research or advertising purposes ("analytics and marketing") and to provide you with additional functions (“functional”). This may result in the creation of pseudonymous usage profiles and the transfer of personal data to third countries, including the USA, which may have no adequate level of protection for the processing of personal data.

By clicking “Accept all”, you consent to the storage of cookies and the processing of personal data for these purposes, including any transfers to third countries. By clicking on “Decline all”, you do not give your consent and we will only store cookies that are necessary for our website. You can customize the cookies we store on your device or change your selection at any time - thus also revoking your consent with effect for the future - under “Manage Cookies”, or “Cookie Settings” at the bottom of the page. You can find further information in our Privacy Policy.
Accept all
Decline all
Manage cookies
✕

Privacy Preference Center

With your consent, we and third-party providers use cookies and similar technologies on our website to analyse your use of our site for market research or advertising purposes ("analytics and marketing") and to provide you with additional functions (“functional”). This may result in the creation of pseudonymous usage profiles and the transfer of personal data to third countries, including the USA, which may have no adequate level of protection for the processing of personal data. Please choose for which purposes you wish to give us your consent and store your preferences by clicking on “Accept selected”. You can find further information in our Privacy Policy.

Accept all cookies

Manage consent preferences

Essential cookies are strictly necessary to provide an online service such as our website or a service on our website which you have requested. The website or service will not work without them.

Performance cookies allow us to collect information such as number of visits and sources of traffic. This information is used in aggregate form to help us understand how our websites are being used, allowing us to improve both our website’s performance and your experience.

Google Analytics

Google Ads

Bing Ads

Facebook

LinkedIn

Quora

Hotjar

Functional cookies collect information about your preferences and choices and make using the website a lot easier and more relevant. Without these cookies, some of the site functionality may not work as intended.

HubSpot

Social media cookies are cookies used to share user behaviour information with a third-party social media platform. They may consequently effect how social media sites present you with information in the future.

Accept selected