With over a thousand votes on the Microsoft Connect site, JSON support is the most requested feature for SQL Server 2016. This month, Microsoft announced that the upcoming release of SQL Server 2016 will fulfill that request. Sort of.
Microsoft will certainly be touting this feature as an additional reason to upgrade when the time comes. Natively supporting JSON helps bridge the gap between the desirable aspects of a NoSQL database and a relational database like SQL Server. The trouble is that with this current implementation, you're not really gaining anything over what's currently available. It's nothing like the native XML data type that became available starting with SQL Server 2005.
The announcement starts off with a big caveat: they will be providing native JSON support, but not a native JSON type. In fact, storage of JSON data will happen the same way it happens today, in a NVARCHAR typed column. They list 3 hollow reasons for this:
- Backward compatibility
- Cross feature compatibility
- Non Microsoft controlled JSON parsers on the client (C#)
Backward compatibility is weak, if you're already storing JSON data you wouldn't have a hard time moving into the JSON type. Cross feature compatibility means that they're not interested in implementing JSON in other SQL Server components, so instead everything that already works with NVARCHAR (aka everything) will still work (aka nothing's changed). The client side JSON parser point is an odd one and it leads into my next gripe of this feature implementation.
On the client side, such as in a C# application, it's already common to serialize data back and forth in JSON using a JSON parsing library. The most common, though maybe not the fastest, is the Newtonsoft JSON.Net library which comes packaged with the default templates in ASP.NET projects etc. The fact that there are other options out there doesn't really matter in my opinion. Sure some behave differently, but in the end, a JSON object has a specific syntax and any proper parser should be able to serialize and deserialize the output of any other parser.
What's baffling are the features that are being supported in this native JSON support.
You can now use FOR JSON to export the results of a query as JSON. This means that you can make a normal T-SQL query and ask for the result back as a JSON formatted result. OK, but I could have just serialized the result on the client side in literally one line of code. They use the example of returning the results directly from an OData request through a web service.
You can transform a JSON object to a relational table with OPENJSON. This means that within a T-SQL query, you can provide a JSON object as part of the query and each item in the JSON will be returned as a table row which can be used to query or insert records into a relational table. They use the example of loading a JSON document into rows of a database, but again, that can easily be done on the client site by deserializing the JSON Array first.
Finally they are providing some built in functions for processing JSON data in the database. ISJSON will check if a NVARCHAR column has JSON data and JSON_VALUE which provides some scalar selection ability similar to the dot notation of JavaScript, e.g.
$.property1.property2.array1[5].property3.array2[15].property4
That's basically the least they could do with this implementation. And as for indexing of JSON data, you're left with some basic support on the level of full-text indexing of any other NVARCHAR column.
So in the end, this "native JSON support" is basically nothing more that a few convenience function on top of a normal NVARCHAR column. It takes a familiar eye about 2 minutes to uncover the sad truth about this feature and the response has shown that Microsoft isn't fooling anybody with this JSON implementation. That's not to say there aren't great features coming in SQL Server 2016 (stretch database seems pretty cool), but JSON support isn't one of them.