JSON Support Is The Best New Developer Feature in SQL 2016 — Part 2: Creating JSON
This is the second article in my series about learning how to use SQL Server 2016’s new JSON functions. If you haven’t already, you can read Part 1 — Parsing JSON.
Last time we looked at SQL 2016’s new functions for parsing JSON data. Today, I want to explore the different options available for creating JSON data from a SQL result set.
The first option we have for creating JSON is by hardcoding a SQL string value. This is a terribly painful way to creating JSON and doesn’t use any of SQL 2016’s new functionality. However, for the sake of completeness I thought it would good to highlight the obvious:
Fortunately, SQL 2016 makes it much easier to generate JSON data from a query’s result set. First, let’s create some data to play around with:
The data above should look pretty familiar from Part 1 of this series. It’s only two rows and a handful of columns, but it should get the job done at showing how to use SQL’s two new JSON creating functions.
First up is the clause FOR JSON AUTO. This clause will take the results of a query and format them into very basic JSON. Not much magic here, but it sure beats having to hardcode (or build dynamic JSON strings) using the first method outlined above.
FOR JSON AUTO does offer some formatting flexibility though as shown in the examples: nesting JSON objects is possible through joining of tables.
Although FOR JSON AUTO is perfect for quick and dirty JSON string creation, SQL offers much more customization with FOR JSON PATH. TheFOR JSON PATH clause will take column aliases into consideration when building the JSON structure, making it unnecessary to have to join data in order to get a nested JSON schema.
And that’s it for creating JSON data in SQL Server 2016. Stay tuned over the next few weeks as we look at other SQL JSON functions that will help us modify data as well as a comparison of how SQL’s JSON functions perform against other languages JSON serialization/deserialization methods.
