SQL

If you are looking for ways to generate JSON files from SQL server data or if you need to pass JSON data to downstream systems, then you are reading the proper blog! This blog describes how to utilize the FOR JSON feature of SQL server to generate JSON data, and eventually JSON file with well-formatted JSON data in it.

Generating JSON data is straightforward. The syntax is as follows:

Select
contactid,
firstname,
lastname
From contact
FOR  JSON  PATH

This is as simple as appending your SQL select query with the FOR JSON clause. Multiple options are available for the structure of the output, including FOR JSON PATH and FOR JSON AUTO. More information can be found here: Microsoft Documentation.

To generate files out of the retrieved JSON data, a little bit of coding is necessary, as SQL returns chunks of rows of JSON data and each row is not a complete JSON. To combine the JSON data retrieved by the FOR JSON clause to generate complete JSON, use a small piece of C# code as follows:

var queryWithForJson = “SELECT … FOR JSON”;
var conn = new SqlConnection(“<connection string>”);
var cmd = new SqlCommand(queryWithForJson, conn);
conn.Open();
var jsonResult = new StringBuilder();
var reader = cmd.ExecuteReader();
if (!reader.HasRows)
{
    jsonResult.Append(“[]”);
}
else
{
    while (reader.Read())
    {
jsonResult.Append(reader.GetValue(0).ToString());
    }
}

One thing to keep in mind is that if the result of the SQL query is too large, the JSON builder might produce a memory exception. To avoid this, open a file and start appending incoming rows into it as the code loops through each retrieved row. Example code snippet:

File.AppendAllText(path, jsonResult);

Hopefully this helps you generate a JSON document from a SQL query.

As always, happy D365’ing!