Page 12 - MSDN Magazine, April 2017

Cutting EdgE DINO ESPOSITO Query JSON Data in SQL Server 2016
Moving data around independent and autonomous systems is all that most software does these days and JSON is the ubiquitous lan- guage behind data transfer. Short for JavaScript Object Notation, JSON is a text-based way to lay out the state of an object so that it can be easily serialized and transferred across the wire from one system to the next, especially in heterogeneous systems.
JSON has become what, in the end, XML failed to be—the lingua franca of the Web. Personally, I wouldn’t buy into much of the fact that JSON is easier to read than XML. On the other hand, JSON is a text format much more compact and lightweight than XML, editable by humans and quick to parse and understand for com- puters across a long list of software and hardware platforms.
A JSON string is a plain text string and any versions of any relational database management system (RDBMS), including SQL Server, let you store a string regardless of its content layout. SQL Server 2016, however, is the first version of the Microsoft database that lets you read existing tabular data as JSON, to save tabular data as JSON and, more important, to query within JSON strings as if the JSON content were actually a collection of individual columns.
For a structured and comprehensive overview of the JSON functions in SQL Server 2016, read the MSDN documentation at bit.ly/2llab1n. In addition, you can find an excellent executive sum- mary of JSON in SQL Server 2016 in the Simple Talk article at bit.ly/26rprwv. The article offers a more business-oriented view of JSON in SQL Server 2016 and, in general, a scenario-based per- spective of the use of JSON data in a relational persistence layer.
JSON Data in the Persistence Layer
Two verbs are key to understanding the purpose of JSON: trans- mit and serialize. Therefore, JSON is the format in which you lay out the state of a software entity so that it can be transmitted across process spaces with the certainty it’ll be well understood on both ends. Great, but this is a column about JSON in SQL Server and, hence, in the persistence layer. So, let’s start with the base question: When would you save data in SQL Server as JSON?
A relational database table is articulated on a fixed number of columns and each column has its own data type, such as strings of variable or fixed length, dates, numbers, Booleans and the like. JSON is not a native data type. A SQL Server column that contains JSON data from the database perspective is a plain string column. You can write JSON data to a table column as you would write a regular string and you can do that in any versions of SQL Server, as well as in any other RDBMS.
Where do you get the JSON strings you eventually store into a database? There are two main scenarios: First, those strings might
comefromaWebserviceorsomeotherformofanexternalendpoint that transmits data (for example, a connected device or sensor). Second, JSON data might be a convenient way to group together related pieces of information so that they appear as a single data item. This typically happens when you deal with semi-structured data, such as data that represents a business event to store in an event-sourcing scenario or, more simply, in a business context that’s inherently event-driven, such as real-time systems for domains such as finance, trading, scoring, monitoring, industrial automa- tion and control, and so on. In all these cases, your storage can be normalized to a structured form serializing related information variable in length and format in a single data item that would fit in the string column of a relational table.
Two verbs are key to understanding the purpose of JSON: transmit and serialize.
As mentioned, the JSON content you might persist can come from an external source or can be generated through serialization from instances of C# objects:
foreach (var c in countries) {
// Serialize the C# object to JSON
var json = JsonConvert.SerializeObject(c);
// Save content to the database record.JsonColumn = json;
You can use Entity Framework (EF), as well, to save JSON data into one column of a database table.
SQL Server 2016 takes this one level further and lets you transform JSON data in table rows. This ability might save a lot of work and CPU cycles off your code as now you can push the raw JSON text to the database without first parsing it to C# objects in the application code and then passing through EF or direct ADO.NET calls. The key to achieve this goal is the new OPENJSON function:
declare @country nvarchar(max) = '{ "id" : 101,
"name": "United States", "continent": "North America"
}';INSERT INTO Countries
SELECT * FROM OPENJSON(@country) WITH (id int,
name nvarchar(100), continent nvarchar(100))
8 msdn magazine
}