Friday, 26 October 2018

Export to JSON from MySQL All Ready for MongoDB

This post walks through how to export data from MySQL® into JSON format, ready to ingest into MongoDB®. Starting from MySQL 5.7+, there is native support for JSON. MySQL provides functions that actually create JSON values, so I will be using these functions in this article to export to JSON from MySQL:
  • JSON_OBJECT
  • JSON_ARRAY
These functions make it easy to convert MySQL data to JSON e.g.
In this article, I will be using the employees sample database available from here:
https://dev.mysql.com/doc/employee/en/employees-installation.html
The employees schema:
Employee schema from MySQL https://dev.mysql.com/doc/employee/en/images/employees-schema.png
When mapping relations with collections, generally there is no one to one mapping, you would want to merge data from some MySQL tables into a single collection.

Export data to JSON format

To export data, I have constructed the following SQL (the data is combined from 3 different tables: employees, salaries, and departments):
You can see from this that json_object did not convert ‘hire_date’ column value to be compatible with MongoDB.  We have to convert date into ISODate format:
Next, we dump the output to a file (the above query is slightly modified) e.g.

Importing data

To load the file  employees.json  into MongoDB, I use the mongoimport utility.  It’s a multi-threaded tool that can load large files efficiently.

Validate

We have successfully migrated some data from MySQL to MongoDB!
The content in this blog is provided in good faith by members of the open source community. The content is not edited or tested by Percona, and views expressed are the authors’ own. When using the advice from this or any other online resource test ideas before applying them to your production systems, and always secure a working back up.

2 comments: