SQL SERVER – JSON Support for Transmitting Data for SQL Server 2016 – Notes from the Field #114

My favorite aspect of working with SQL Server is the T-SQL language. I love working with data, writing queries, and tuning queries when they don’t perform well. Whenever a new version of SQL Server is on the way, I can’t wait to hear what has been added to T-SQL.

I was really hoping to see additional T-SQL windowing functionality with 2016, but, so far, nothing has been announced. There is however, a very interesting programmability enhancement on the way: JSON support.

JSON, which stands for JavaScript Object Notation, is used for transmitting data and is used in many programming languages. JSON looks a bit like XML with square [] and curly {} brackets instead of angle <> brackets.

Here is an example comparing XML to JSON with data from the Production.Product table.

<Product>
  <Name>HL Road Frame - Black, 58</Name>
  <ListPrice>1431.5000</<wbr />ListPrice>
</Product>
<Product>
  <Name>HL Road Frame - Red, 58</Name>
  <ListPrice>1431.5000</<wbr />ListPrice>
</Product>
<Product>
  <Name>Sport-100 Helmet, Red</Name>
  <ListPrice>34.9900</ListPrice>
</Product>
<Product>
  <Name>Sport-100 Helmet, Black</Name>
  <ListPrice>34.9900</ListPrice>
</Product>
<Product>
  <Name>Mountain Bike Socks, M</Name>
  <ListPrice>9.5000</ListPrice>
</Product>

{</pre>
<pre>"Product": [
{
"Name": "HL Road Frame - Black, 58",
"ListPrice": 1431.5
},
{
"Name": "HL Road Frame - Red, 58",
"ListPrice": 1431.5
},
{
"Name": "Sport-100 Helmet, Red",
"ListPrice": 34.99
},
{
"Name": "Sport-100 Helmet, Black",
"ListPrice": 34.99
},
{
"Name": "Mountain Bike Socks, M",
"ListPrice": 9.5
}
]
}

The additions to T-SQL to support JSON are listed below. There is one new clause and

four functions:

I used the FOR JSON clause to generate the example JSON data above. Here is the code I used:

SELECT TOP(5) Name, ListPrice
FROM Production.Product
WHERE ListPrice > 0 AND Name NOT LIKE '%Seat%'
FOR JSON PATH, ROOT('Product');

There are several options, so be sure to take a look at the documentation for more information. Here is an example script demonstrating the functions:

--Save a string in JSON format to a variable
DECLARE @Books NVARCHAR(MAX) = N'
{"Books":
{"BookList":
[	{"Book":
{	"Title":"Microsoft SQL Server T-SQL Fundamentals",
"Authors":"Itzik Ben Gan"
}
},	
{"Book":
{
"Title":"The Data Warehouse Toolkit: The Definitive Guide to Dimensional Modeling",
"Authors":"Ralph Kimball; Margy Ross"
}
},
{"Book":
{
"Title":"SQL Server Concurrency: Locking, Blocking, and Row Versioning",
"Authors":"Kalen Delaney"
}
}	
]
}	
}';
IF ISJSON(@Books) = 0 BEGIN 
   PRINT 'Invalid JSON string';
END 
ELSE BEGIN
   --Turn the JSON string into tabular format
   SELECT * 
   FROM OPENJSON(@Books,'$.Books.BookList')
   WITH (
       Title NVARCHAR(100) '$.Book.Title',
       Authors NVARCHAR(100) '$.Book.Authors');
   --Return the BookList array in JSON format
   SELECT JSON_QUERY(@Books,'$.Books.BookList') AS [Book Array];
   --Return the second row as JSON
   SELECT JSON_QUERY(@Books,'$.Books.BookList[1]') AS [Second Book];
   --Return the title from the second row
   SELECT JSON_VALUE(@Books,'$.Books.BookList[1].Book.Title') AS [Book Title];
END;

Here are the results:

The most challenging aspect for me when writing the previous example, was creating a valid JSON string. The path attribute, that string starting with $ used with the functions, looks a bit intimidating, but it is actually simple. The $ is the starting point. Follow the path in the data to get what you want.

Let’s take a look at the last query:

SELECT JSON_VALUE(@Books,'$.Books.<wbr />BookList[1].Book.Title') AS [Book Title];

The JSON_VALUE function takes two parameters, the JSON string and a path. In this case, I am specifying the item from the array to extract. It is found in Books and the array is called BookList. I want the item at index 1. The item is called Book and I am retrieving the Title property.

The new functionality will make it easier for developers to work with SQL Server data regardless of the programming language they use.

Kathi Kellenberger is the author of Beginning T-SQL, Expert T-SQL Window Functions in SQL Server, and PluralSight course T-SQL Window Functions.

If you would like to learn more about T-SQL window functions, be sure to check out my latest book Expert T-SQL Window Functions in SQL Server or my Pluralsight course T-SQL Window Functions.

If you want to get started with SQL Server with the help of experts, read more over at Fix Your SQL Server.