Since this is a standard file format, you should be able to generate data in JSON format from SQL Server.
Fortunately, since SQL Server 2016, there is a native way to do it, in this post I will show you how to do it.
Generate a simple JSON file
The most basic syntax to generate a JSON file is this:
Using this simple test table with 10 records as an example:
If we use JSON AUTO on this sample data we will have the following output:
|Part of the JSON AUTO output|
Using dot syntax
For most of real-world applications, the JSON AUTO will not give you the control you could need over your file format, for having more control over it, you must use the JSON PATH option, along with the ROOT option as follows:
This will generate the following output:
|JSON PATH output|
And if we want to group similar items (for example for queries with joins), just rename the fields as element.field as in this example:
Will generate the following output:
|JSON PATH with grouped items|
Of course, if you have SQL Server Operations Studio you can do it from the IDE:
If you want to learn more about the FOR JSON option, please read Microsoft official documentation here