Migrating, JSON, MySQL – Migrating data from one API to another

Well as usual, I am just storing a post to keep track of some practices. As I know that maybe in 6 months or 2 years, I may have to do this kind of quick and dirty job again.

My issue was the following:

I have data stored in a third party API and due to budget cuts, I need to backup and migrate this data to a less expansive solution. In order to do so, I have made up a small « ToDoList » where I have written down the following steps :

  1. Making a dump of the data from the API. The dump consists a bunch of JSON files of 100 records. As you can imagine, I have 200 000 users recorded in the API, so basically I have 2 000 JSON files to parse. I just gave 3 sample files with fake data as example.
  2. Studying the JSON output in order to parse it with PHP and grab the data in order to insert into a database. I have chosen MySQL with the help of PHP-Cli to script the all insertion.
  3. Iterate via a command line to repetitively do the injection of each record with the minimum of programming. I will leverage on Bash to do so even though I have tried to work with Automator in the first time.

For the step 1, I had to mock-up the JSON output of the API and generate fake data with the help of json-generator.com. I give also the website that I am using to validate the JSON structure : jsonlint.com

The script for json-generator.com

    email: '{{firstName().toLowerCase()}}.{{surname().toLowerCase()}}@{{company().toLowerCase()}}.com',
    mojoinc: {
      properties: {
        managedBy: [{
          clientId: '{{guid()}}',
          id: '{{integer(1000000, 1000000000)}}'
    uuid: '{{guid()}}'

The files are the following:

  • mojoinc_backup_all.sql: a quick scheme of the database that will receive the output of the JSON files.
  • parse_and_insert.php: the script that enable the insert into the DB from the JSON files.
  • lauch_sh_insert.sh: the bash script and the php file that help to generate it: launch_phpcli_parse_and_insert.php
  • Some JSON with fake data: dump_generated_1.json, dump_generated_2.json, dump_generated_3.json

The files can be found @https://github.com/bflaven/BlogArticlesExamples/tree/master/parse_json_inject_mysql

Create JSON with the JSON Generator
Migrating, JSON, MySQL - Migrating data from one API to another

JSON validated with JSONLint
Migrating, JSON, MySQL - Migrating data from one API to another

Read more