MySQL JSON Export from Table or Query result

In this article we will learn how to use ZappyShell Command Line Tools for MySQL JSON Export from Table or Query result

JSON File format is becoming very popular due to its simplicity and smaller size. Most of NoSQL Databases are now using JSON for their document mechanism.

To export data from MySQL we will need ODBC Driver. In this tutorial we have use MySQL ODBC 5.2 Driver but if you are using different driver then change connection string accordingly.

Download and Install MySQL ODBC Driver from below URL
http://dev.mysql.com/downloads/connector/odbc/

Download MySQL 5.2 ODBC Driver

MySQL JSON Export – Install MySQL ODBC Driver

Now next step is we need to Download and Install ZappyShell Command Line tools from hereYou can Download MSI Installer or XCopy Zip file which you can simply extract and copy (see below).

ZappyShell Command Line Tools – Export JSON, Excel, CSV, PDF

Lets say you want to export customer data along with orders they placed as nested JSON document then we have to supply Map file so JSON Generator can figure out how to JOIN two datasets and how to Shape JSON. In below example we are doing 3 nested levels for demo purpose. Customer joined with Orders by CustomerID key, Orders Joined with Employee by EmployeeID key.

MySampleMap.xml

<?xml version="1.0" encoding="UTF-8"?>
<settings ver="1">
  <dataset id="cust" main="true">select * from Customers</dataset>
  <dataset id="ord">select * from Orders</dataset>
  <dataset id="emp">select * from Employees</dataset>
  <join parentid="cust" childid="ord" keys="CustomerID" />
  <join parentid="ord" childid="emp" keys="EmployeeID" />

  <map src="CustomerID" name="ID" />
  <map src="CompanyName" />
  <map name="Contact">
    <map src="Phone" />
    <map src="Fax" />
    <map name="Location">
      <map src="Country" />
      <map src="Region" />
    </map>
  </map>

  <map name="OrderIDList" dataset="ord" maptype="valuearray" src="OrderID"/>
  <map name="Orders" dataset="ord">
    <map src="OrderID" />
    <map src="OrderDate" />
    <map name="EmpInfo" dataset="emp">
      <map src="EmployeeID" />
      <map src="FirstName" />
    </map>
  </map>
</settings>

c:\tools>db.exe export --map c:\MySampleMap.xml --out c:\data\customers.json --connstr "Driver={MySQL ODBC 5.2 ANSI Driver};Server=your_server_name_or_ip;Port=3306;Uid=user1;pwd=SomeP@ssword;Database=your_db_name"

customers.json : Output JSON generated from above Command

[
  {
    "ID": "ALFKI",
    "CompanyName": "Alfreds Futterkiste",
    "Contact": {
      "Phone": "030-0074321",
      "Fax": "030-0076545",
      "Location": {
        "Country": "Germany",
        "Region": null
      }
    },
    "OrderIDList": [10643,10692],
    "Orders": [
      {
        "OrderID": 10643,
        "OrderDate": "1997-08-25",
        "EmpInfo": [
          {
            "EmployeeID": 6,
            "FirstName": "Michael"
          }
        ]
      },
      {
        "OrderID": 10702,
        "OrderDate": "1997-10-13",
        "EmpInfo": [
          {
            "EmployeeID": 4,
            "FirstName": "Margaret"
          }
        ]
      }
    ]
  },
  {
    "ID": "ANATR",
    "CompanyName": "Ana Trujillo Emparedados y helados",
    "Contact": {
      "Phone": "(5) 555-4729",
      "Fax": "(5) 555-3745",
      "Location": {
        "Country": "Mexico",
        "Region": null
      }
    },
    "OrderIDList": [10308,10625,10759,10926],
    "Orders": [
      {
        "OrderID": 10308,
        "OrderDate": "1996-09-18",
        "EmpInfo": [
          {
            "EmployeeID": 7,
            "FirstName": "Robert"
          }
        ]
      },
      {
        "OrderID": 10625,
        "OrderDate": "1997-08-08",
        "EmpInfo": [
          {
            "EmployeeID": 3,
            "FirstName": "Janet"
          }
        ]
      }
    ]
  }
]

Sometimes you may have need to split large amount of data into multiple files. With ZappyShell command line you can split data three different ways. Lets check each example.

MySQL JSON Export – Split By Row Count

c:\tools>db.exe export customers --out c:\data\cust.json --split --splitrows 1000 --connstr "Driver={MySQL ODBC 5.2 ANSI Driver};Server=your_server_name_or_ip;Port=3306;Uid=user1;pwd=SomeP@ssword;Database=your_db_name"

MySQL JSON Export – Split Files By File Size

c:\tools>db.exe export customers --out c:\data\cust.json --split --splitsize 10MB --connstr "Driver={MySQL ODBC 5.2 ANSI Driver};Server=your_server_name_or_ip;Port=3306;Uid=user1;pwd=SomeP@ssword;Database=your_db_name"

MySQL JSON Export – Split Files By Group By Column

In this example we have exported customers in to separate file by country.

c:\tools>db.exe export customers --out c:\data\cust.json --groupby Country --connstr "Driver={MySQL ODBC 5.2 ANSI Driver};Server=your_server_name_or_ip;Port=3306;Uid=user1;pwd=SomeP@ssword;Database=your_db_name"

ZappyShell – Export MySQL Table to JSON, Split files by Country Column

 

If you want to output compressed JSON (GZIP format) then use below command.

c:\tools>db.exe export customers --out c:\data\cust.json --gzip --connstr "Driver={MySQL ODBC 5.2 ANSI Driver};Server=your_server_name_or_ip;Port=3306;Uid=user1;pwd=SomeP@ssword;Database=your_db_name"

If you want to use date time place holders in file name so it automatically uses specified date time parts use below command simply putting specifiers in file name anywhere.

c:\tools>db.exe export customers --out c:\data\cust_{{YYYY}}_{{MM}}_{{DD}}_{{HH24}}_{{MI}}_{{SS}}.json --connstr "Driver={MySQL ODBC 5.2 ANSI Driver};Server=your_server_name_or_ip;Port=3306;Uid=user1;pwd=SomeP@ssword;Database=your_db_name"

c:\tools>db.exe export customers --out c:\data\cust.json --json-multi-content --connstr "Driver={MySQL ODBC 5.2 ANSI Driver};Server=your_server_name_or_ip;Port=3306;Uid=user1;pwd=SomeP@ssword;Database=your_db_name"

• Download ZappyShell