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:

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.

-- AUTO will format a result into JSON following the same structure of the result set
SELECT Make, BaseModel, Trim, Year, PurchaseDate
FROM ##Garage
FOR JSON AUTO;
-- Output: [{"Make":"Volkswagen","BaseModel":"Golf","Trim":"GL","Year":2003,"PurchaseDate":"2006-10-05T00:00:00"},{"Make":"Subaru","BaseModel":"Impreza","Trim":"Premium","Year":2016,"PurchaseDate":"2015-08-18T00:00:00"}]
-- Using aliases will rename JSON keys
SELECT Make AS [CarMake]
FROM ##Garage
FOR JSON AUTO;
-- Output: [{"CarMake":"Volkswagen"},{"CarMake":"Subaru"}]
-- Any joined tables will get created as nested JSON objects. The alias of the joined tables becomes the name of the JSON key
SELECT g1.Make, Model.BaseModel as Base, Model.Trim, g1.Year, g1.PurchaseDate
FROM ##Garage g1
INNER JOIN ##Garage Model on g1.Id = Model.Id
FOR JSON AUTO;
-- Output: [{"Make":"Volkswagen","Year":2003,"PurchaseDate":"2006-10-05T00:00:00","Model":[{"Base":"Golf","Trim":"GL"}]},{"Make":"Subaru","Year":2016,"PurchaseDate":"2015-08-18T00:00:00","Model":[{"Base":"Impreza","Trim":"Premium"}]}]
-- Finally we can encapsulate our entire JSON result in a parent element by specifiying the ROOT option
SELECT Make, BaseModel, Trim, Year, PurchaseDate
FROM ##Garage
FOR JSON AUTO, ROOT('Cars');
-- Output: {"Cars":[{"Make":"Volkswagen","BaseModel":"Golf","Trim":"GL","Year":2003,"PurchaseDate":"2006-10-05T00:00:00"},{"Make":"Subaru","BaseModel":"Impreza","Trim":"Premium","Year":2016,"PurchaseDate":"2015-08-18T00:00:00"}]}

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.

As a developer my favorite new feature of SQL Server 2016 is JSON support.

I love JSON in SQL because I already love JSON everywhere outside of SQL: it uses much less space than XML for serializing data, it’s what most apps are now using for API communication, and when web developing I love that it is already valid JavaScript (no need to deserialize!).

I had this same type of excitement for XML in SQL Server, but after using it the excitement quickly turned into disappointment: having to constantly use the XML datatype was inconvenient (when most XML data I used was already stored in nvarchar(max) columns) and I never found the syntax of OPENXML() to be that intuitive.

Everything I’ve done with JSON in SQL Server 2016 so far has been great. I’ve already been storing persistent JSON in SQL, so being able to manipulate JSON within SQL is even better. In this series of posts I will go over the various functionalities of using JSON in SQL Server 2016:

Part 1 — Parsing JSON

What is JSON?

My simple, mostly caveat-free* explanation is that it is a format for storing object data in JavaScript. It’s lightweight and easy to read, so it’s used in lots of applications that aren’t just JavaScript (although it’s especially easy to consume in JavaScript because it is JavaScript*).

Strict versus Lax mode

For any of the SQL JSON functions (OPENJSON(), JSON_VALUE(), JSON_QUERY(), andJSON_MODIFY()) you can specify whether invalid JSON paths will return NULL or an error. The default value is lax, which will return a NULL for non-existing JSON paths, whereas strict will return an error message.

OPENJSON()

While JSON_VALUE() extracts singular scalar values and JSON_QUERY() extracts singular JSON strings, the OPENJSON() function extracts result sets from a JSON string. In addition to the extracted value, OPENJSON() outputs the order of JSON objects as well as their datatypes. OPENJSON() will also output string representations of JSON arrays instead of just displaying NULL, similar to JSON_QUERY().

The flexibility of OPENJSON() makes it possible to extract any values from JSON data, especially when combining OPENJSON() with JSON_VALUE(). The examples below show how to parse out a scalar value from complex JSON objects (like arrays). Note that using the WITH option gives us a lot more flexibility with how we can format our output result.

These JSON functions should help you parse any JSON data you encounter in SQL server (as long as it’s valid and stored as nvarchar). Stay tuned over the next few weeks as we look at other SQL JSON functions that will help us create data, modify data, and compare SQL’s JSON performance to other languages.