If you’ll ever try to generate JSON file using Sql Server’s Management Studio, probably there’ll be lots of pain in your future. If you wanna save the pain, you can use SQL2JSON, an open source project I started last week.
The idea was born out of a need to generate a huge JSON file containing data from MSSQL database. After seeing our DBA struggling with formatting, escaping weird characters and unicode strings, I decided to write a small utility that will do just that - execute a sql query and capture its results as a json file.
You can grab it from Github repository.
Usage
Suppose you have a table called “users” with the following records:
|user_id | first_name | last_name
|--------|------------|----------
|1 | john | johnson
|2 | scott | scottson
|3 | paul | paulson
Example #1 - Simplest Case
sql2json.exe -cs="Data Source=.;Initial Catalog=DB1;User Id=usr;Password=pwd;" -sql="select * from users" -output="users.json"
The output file, users.json:
[
{ "user_id": 1, "first_name": "john", "last_name": "johnson"},
{ "user_id": 2, "first_name": "scott", "last_name": "scottson"},
{ "user_id": 3, "first_name": "paul", "last_name": "paulson"}
]
Example #2 - Nested Objects
This example demonstrates the use of delimiters to build a json string containing nested objects
sql2json.exe -cs="Data Source=.;Initial Catalog=DB1;User Id=usr;Password=pwd;" -sql="select user_id, first_name as 'name::first', last_name as 'name::last' from users" -output="users.json"
Results in:
[
{
"user_id": 1,
"name": { "first": "john", "last": "johnson" }
},
{
"user_id": 2,
"name": { "first": "scott", "last": "scottson" }
},
{
"user_id": 3,
"name": { "first": "paul", "last": "paulson" }
}
]
Advanced Scenarios
If you need to include custom calculations or aggregations in your JSON objects you can either pre-calculate them in your sql query, or if you are more adventurous, you can write your own implementation of ITransformer to execute the required logic.