SQL Server JSON Export from Table or Query Data

Introduction

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. In this article we will learn how to use various techniques to generate JSON data files from SQL Server Table or any other relational source such as MySQL or Oracle. This article is mainly focused on exporting data from Microsoft SQL Server to JSON but you can use same tools/techniques to export from any RDBMS (As long as you have correct drivers installed.).

The problem

There is no native way of Exporting SQL Server Data to JSON. You can use some coding techniques or libraries but when things become complex you will run out of options quickly.

Solution

There are two different approach you can take depending on your need.
1) Using SSIS PowerPack to generate JSON (Preferred approach if you are using SSIS)
2) Using ZappyShell for DB Command Line to generate JSON file (Use this when you are not using SSIS).

Export SQL Server data to JSON using SSIS PowerPack

If you want to use SSIS as your ETL Tool to automate JSON Export then Download SSIS PowerPackOnce SSIS PowerPack is installed you can perform the steps described in the following video to generate desired JSON File.

Export SQL Server data to JSON using ZappyShell Command line

If you are not using SSIS to export JSON and you wish to automate that process then you can use ZappyShell Command Line tools to export any Relational Data into nested JSON.First you 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

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

SQL Server JSON Export – Single table

c:\tools>db.exe export customers --out c:\data\customers..json --json --overwrite --connstr "Data Source=localhost;Initial Catalog=Northwind;Integrated Security=SSPI;"

SQL Server JSON Export – Multiple tables

In the following example we are exporting 3 tables (seperated by vertical bar)
c:\tools>db.exe export customers|orders|products --out c:\data\customers..json --json --overwrite --connstr "Data Source=localhost;Initial Catalog=Northwind;Integrated Security=SSPI;"

SQL Server JSON Export – Multiple tables (using pattern)

In below example all tables where name staring with cust will be exported from sales schema.
c:\tools>db.exe export cust% --schema Sales --out c:\data\customers..json --json --overwrite --connstr "Data Source=localhost;Initial Catalog=Northwind;Integrated Security=SSPI;"

SQL Server JSON Export – SQL Query

In the following example we are exporting output of SQL Query to JSON file. If you have complex query with multi line then you can use Map file with one dataset node.c:\tools>db.exe export "select * from customers where country like 'US%'" --out c:\data\customers.json --json --overwrite --connstr "Data Source=localhost;Initial Catalog=Northwind;Integrated Security=SSPI;"

Using Map File for nested SQL Server JSON Export

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 "Data Source=localhost;Initial Catalog=Northwind;Integrated Security=SSPI;"

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"
          }
        ]
      }
    ]
  }
]

SQL Server JSON Export – Split Files

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.

SQL Server JSON Export – Split By Row Count

In below example we are exporting SQL Server customer table to JSON files and splitting files with maximum 1000 records per file option.

c:\tools>db.exe export customers --out c:\data\cust.json --split --splitrows 1000 --connstr "Data Source=localhost;Initial Catalog=Northwind;Integrated Security=SSPI;"

SQL Server JSON Export – Split Files By File Size

In below example we are exporting SQL Server customer table to JSON files and splitting files with maximum file size = 10MB option. You also use other size specifier like KB, GB, BYTES etc.

c:\tools>db.exe export customers --out c:\data\cust.json --split --splitsize 10MB --connstr "Data Source=localhost;Initial Catalog=Northwind;Integrated Security=SSPI;"

SQL Server 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 "Data Source=localhost;Initial Catalog=Northwind;Integrated Security=SSPI;"
ZappyShell - Export SQL Server Table to JSON, Split files by Country Column

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

 

SQL Server JSON Export – Compressed files

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 "Data Source=localhost;Initial Catalog=Northwind;Integrated Security=SSPI;"

Using date time place holders in JSON file 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 "Data Source=localhost;Initial Catalog=Northwind;Integrated Security=SSPI;"

SQL Server JSON Export – Multi Content format

To export data in Multi-Content JSON format use below command. Multi content format append each record as JSON document (no opening closing array brackets)

c:\tools>db.exe export customers --out c:\data\cust.json --json-multi-content --connstr "Data Source=localhost;Initial Catalog=Northwind;Integrated Security=SSPI;"

Conclusion

In this article we have gone through few ways to use ZappyShell Command Line Tools to export data from SQL Server to JSON. You can also use same command line to export in CSV, Excel, PDF format.

Reference Links

Posted in SQL Server and tagged , , , , , , .