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.
$ mysqlsh root@localhost:33300/test --import /path_to_file/zips.json
Creating a session to 'root@localhost:33300/test'
Please provide the password for 'root@localhost:33300':
Fetching schema names for autocompletion... Press ^C to stop.
Your MySQL connection id is 21 (X protocol)
Server version: 8.0.13 MySQL Community Server - GPL
Default schema `test` accessible through db.
Importing from file "/path_to_file/zips.json" to collection `test`.`zips` in MySQL Server at localhost:33300
.. 29353.. 29353
Processed 3.15 MB in 29353 documents in 1.2058 sec (24.34K documents/s)
Total successfully imported documents 29353 (24.34K documents/s)
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
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.
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