Buy Percona ServicesBuy Now!

Export to JSON from MySQL All Ready for MongoDB

 | October 16, 2018 |  Posted In: Entry Level, MongoDB, MySQL, Tools

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.

Aftab Khan

IT professional with 10+ years experience, mainly in large commercial environments. Expertise in MySQL, MariaDB, Big Data, Bash , Unix systems and Python development. Aftab's LinkedIn Profile

One Comment

  • but why not just use a tool?

    https://github.com/virtimus/mysql2mongo

    && example

    main.py
    –tables “users,products”
    –host “localhost”
    –user “root”
    –password “MysqlSecRetPassword”
    –database=”shop”
    –mongo “mongodb://localhost:27017”
    –mongodb “MongoDBNameForExport”

Leave a Reply