The latest release of the MySQL Shell 8.0.13 (GA) introduced some interesting improvements and features, for more information see the full changelog here: https://dev.mysql.com/doc/relnotes/mysql-shell/8.0/en/mysql-shell-news-8-0-13.html. One of those features was the introduction of a convenient and easy way to import JSON documents to a MySQL Server database.

Previously, if you wanted to import data that was held by JSON documents to a MySQL database, then you had to implement a script to parse the JSON documents and generate the appropriate INSERT statements, or convert the JSON documents to CSV files to be able to execute a LOAD DATA INFILE statement, or find some other third-party tool that could help you achieve your goal. That could be a bit tedious, right?… Well, not anymore!

Now, you can import JSON documents to a MySQL database in a single operation using the MySQL Shell. Let’s jump into a quick example to show you how fast and simple it is.

In this example, all the JSON documents from file “zips.json” were imported to the ‘zips‘ collection in the ‘test’ schema, automatically creating the collection since it didn’t exist. By default, the name of the file without the extension is used as the target name, but you can specify a different one if you want. The JSON data can also be imported to an existing table and a specific column (by default, the column named ‘doc‘ is used if none is specified). Cool, right?

Using MySQL Shell functions to import JSON to MySQL

Besides using the command line, you can also use the available API to import JSON documents using the MySQL Shell, available for both JavaScript and Python mode, respectively: util.importJson() and util.import_json(). This allows you to integrate the JSON import feature into your own custom (JavaScript or Python) script to migrate data to MySQL, for example to apply some changes to the imported data, using the MySQL Shell to execute your script.

The simplest and quickest way to get information about how to use those new functions is to use the integrated help feature of the MySQL Shell. Follows an example of how to display the help for the function in JavaScript mode (in Python mode use: \h import_json).

As expected, the function requires the specification of the path to the file containing the JSON documents to import and provides options to specify the target schema, collection or table/column. Follows an example of how those options can be used.

First, the file “zips.json” was imported to a collection ‘zips_collection‘, then it was imported to a table ‘zips_table‘, using the JavaScript function util.importJson(). In both cases, none of the target collection and table already existed, so it was automatically created.

The ‘collection‘ option only allows valid collections (or non-existing) to be specified as value, otherwise an error occurs. In the case of importing to an existing table, using the ‘table‘ option, if no specific column is specified with the ‘tableColumn‘ option then by default it is assumed that the ‘doc’ column is the target. The target table column should preferably be of JSON type, and if the table has other columns then they must have a default value or a value that can be automatically calculated when new rows are inserted (e.g. auto increment or generated columns).

The choice of the target structure to import the data is yours, but keep in mind that it will have practical implications on the way you will be able to use the imported data. If you choose a collection as the target of the import you will be able to take advantage of all MySQL Document Store features (NoSQL + SQL). On the other hand, if you choose a table /column as the target of the import you will be “limited” to the available JSON type features and JSON functions natively provided by MySQL to manipulate your JSON data (only SQL).

The JSON import function also provides a ‘convertBsonOid‘ option that is only required if you need to convert JSON from MongoDB, more specifically to convert the BSON ObjectId type in the strict representation of MongoDB Extended JSON. We will provide more details about how to import JSON data from MongoDB to MySQL in a future post.

Try it now and send us your feedback

MySQL Shell 8.0.13 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/

The sample JSON data used in the examples above is publicly available on the following links. You can download it and try it yourself.

• Zips JSON documents: http://media.mongodb.org/zips.json