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.
Suppose you have a table called “users” with the following records:
1 2 3 4 5
Example #1 - Simplest Case
1 2 3 4 5
Example #2 - Nested Objects
This example demonstrates the use of delimiters to build a json string containing nested objects
1 2 3 4 5 6 7 8 9 10 11 12 13 14
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.