Importing Data from MongoDB to MySQL: BSON Data Types

The latest release of the MySQL Shell 8.0.14 (GA) improved the JSON import utility to support the conversion of more BSON data types from the strict mode representation of MongoDB Extended JSON . This removes a previous limitation regarding the import of more complex MongoDB data types to MySQL, making it more reliable. It also makes the JSON import utility even more easy to use, since the conversion is performed seamlessly and you don’t have to worry about those BSON data types anymore. Cool, eh?

The JSON import utility was introduced in the previous MySQL Shell release (8.0.13 GA) and the basics of this feature were described in a previousblog post. Two other blog posts are also available, covering a more practical and advanced usage of the feature: Import Data from MongoDB to MySQL using JavaScript and Import Data from MongoDB to MySQL using Python .

The previous version of the JSON import utility only supported the conversion of the BSON ObjectId type. Now, it supports the conversion of the following additional BSON types:

• Date
• Timestamp
• NumberDecimal
• NumberLong
• NumberInt
• Regular Expression
• Binary

How to use the BSON types conversion

In order to use this new feature, you simply need to set the new ‘ convertBsonTypes ‘ option to true. All the supported BSON data types will be converted to the corresponding MySQL data types so that you can use the proper types in indexes and manipulate them with SQL statements and X DevAPI functions.

NOTE:The ‘convertBsonOid’ option is automatically set with the value of the ‘convertBsonTypes’ option, so you don’t need to set this option to convert the ObjectID if you already set ‘convertBsonTypes’ to true.

Command:

> util.importJson("/path_to_file/neighborhoods_mongo.json", {schema: "test", collection: "neighborhoods", convertBsonTypes: true});

Yes! It is as simple as that.

But that’s not all, because other options are also available for you to use, namely: to ignore the conversion of certain BSON types and import the types the same way as they are represented in the imported JSON file; to convert decimal types to doubles (instead of strings); to extract the timestamp from the ObjectID. A summary of these new  options is provided below.

Advanced BSON conversion options

In some situations, you might want to skip the conversion of some BSON types, in order to handle them (later) in a custom way or because you just want to keep their representation as in the JSON file. When ‘ convertBsonTypes’  is enabled, the following options are available to avoid the conversion of specific BSON types:

• ‘ ignoreDate ‘: skip the conversion of Date BSON types (by default, false);
• ‘ ignoreTimestamp ‘: skip the conversion of Timestamp BSON types (by default, false);
• ‘ ignoreBinary ‘: skip the conversion of Binary BSON types (by default, false);
• ‘ ignoreRegex ‘: skip the conversion of Regular Expression BSON types (by default, false);
• ‘ ignoreRegexOptions ‘: ignore the regular expression options when processing the BSON Regular Expression values (by default, true). This option is only valid if ‘ignoreRegex’ is enables.

The NumberDecimal BSON type is converted to string by default, but you can choose to convert it to double by using the ‘ decimalAsDouble ‘ option, for example:

util.importJson("/path_to_file/neighborhoods_mongo.json", {schema: "test", collection: "neighborhoods", convertBsonTypes: true, decimalAsDouble: true});

The ‘ extractOidTime ‘ option provides another new interesting feature, allowing us to extract the ObjectID timestamp to a new field in the result of the import, simply by specifying the name for the new field. For example, to extract the timestamp of the ObjectID to a “id_timestamp” field, you can simply use the option as follows:

util.importJson("/path_to_file/neighborhoods_mongo.json", {schema: "test", collection: "neighborhoods", convertBsonTypes: true, extractOidTime: "id_timestamp"});

NOTE: The option ‘extractOidTime’ only works for ObjectID values of the main JSON document, i.e., it has no effect on ObjectID values contained by embedded documents.

NOTE:These options are only valid if ‘convertBsonTypes’ is enabled. In the case of the ‘extractOidTime’ option it is valid only if ‘convertBsonOid’ is enabled.

For more information, see the JSON import utility documentation: https://dev.mysql.com/doc/mysql-shell/8.0/en/mysql-shell-utilities-json.html . You can also use the help directly from the MySQL Shell, to easily get additional information about the function, just type: “ \h importJson “.

Try it now and send us your feedback

MySQL Shell 8.0.14 GA is available for download from the following links.

• MySQL developers website: https://dev.mysql.com/downloads/shell/
• MySQL Shell is also available on GitHub: https://github.com/mysql/mysql-shell

The documentation of MySQL Shell can be found here: https://dev.mysql.com/doc/mysql-shell/8.0/en/