Page 14 - MSDN Magazine, April 2017

SQL FORMAT function expects to receive a number and you
receive an error if you pass the direct JSON value. To make it work,
you must resort to an explicit CAST:
SELECT CountryCode, CountryName, FORMAT(CAST(
JSON_VALUE(Serialized, '$.Population') AS int), 'N0')
AS People FROM Countries
WHERE ISJSON(Serialized) > 0 AND JSON_VALUE(Serialized,'$.Population') > 100000000
ORDER BY JSON_VALUE(Serialized, '$.AreaInSqKm')
The JSON_VALUE can only return a single scalar value. If you have an array of a nested object that you want to extract, then you must resort to the JSON_QUERY function.
How effective is it to query over JSON data? Let’s do some tests.
JSON parsing is faster than the deserialization of some special types, such as XML and spatial.
Indexing JSON Content in SQL Server 2016
As obvious as it might sound, querying the entire JSON string from the database and then parsing it in memory through a dedicated library such as Newtonsoft JSON, albeit always functional, might not be an effective approach in all cases. Effectiveness mostly depends on the number of records in the database and how long it might really take to get the data you need in the format you need. Proba- bly for a query that your application runs occasionally, in-memory processing of JSON data might still be an option. In general, though, querying through JSON-dedicated functions and letting SQL Server do the parsing internally results in slightly faster code. The differ- ence is even bigger if you add an index on JSON data.
You shouldn’t create the index on the JSON column, however, as it wouldindextheJSONvalueasasinglestring.You’llhardlybequerying for the entire JSON string or a subset of it. More realistically, instead, you’ll be querying for the value of a particular property in the serial- ized JSON object. A more effective approach is creating one or more computed columns based on the value of one or more JSON proper- ties and then indexing those columns. Here’s an example in T-SQL:
-- Add a computed column
ALTER TABLE dbo.Countries
ADD JsonPopulation
AS JSON_VALUE(Serialized, '$.Population')
-- Create an index
CREATE INDEX IX_Countries_JsonPopulation ON dbo.Countries(JsonPopulation)
Again, you should be aware that JSON_VALUE returns NVARCHAR, so unless you add CAST the index will be created on text.
Interestingly, JSON parsing is faster than the deserialization of some special types, such as XML and spatial. You can find more information at bit.ly/2kthrrC. In summary, at least JSON parsing is better than fetching properties of other types.
JSON and EF
As a general remark, the JSON support in SQL Server 2016 is primarily exposed through the T-SQL syntax, as tooling is quite limited now. In particular, EF doesn’t currently provide any facilities to query JSON data, except for the SqlQuery method in EF6 and FromSql in EF Core. However, this doesn’t mean you can’t serialize complex properties of C# classes (say, arrays) into JSON columns. An excellent tutorial for EF Core can be found at bit.ly/2kVEsam.
Wrapping Up
SQL Server 2016 introduces some native JSON capabilities so that you can more effectively query stored JSON data as a canonical rowset. This mostly happens when the JSON data is the serialized version of some semi-structured aggregate of data. Indexes built out
of computed columns that reflect that value of one or more JSON properties definitely help improve the performance. JSON data is stored as plain text and isn’t considered a special type, such as XML and Spatial. However, this just enables you to use JSON columns in any SQL Server objects right away. The same can’t be said for other complex types such as XML, CLR and Spatial that are still on the waiting list. In this column, I focused on the JSON-to-rowset scenario. However, SQL Server 2016 also fully supports the rowset-to-JSON query scenario when you write a regular T-SQL query and then map results to JSON objects via the FOR JSON clause. For more information on this feature, see bit.ly/2fTKly7. n
Dino Esposito is the author of “Microsoft .NET: Architecting Appli- cations for the Enterprise” (Microsoft Press, 2014) and “Modern Web ApplicationswithASP.NET”(MicrosoftPress,2016).Atechnicalevan- gelist for the .NET and Android platforms at JetBrains, and frequent speaker at industry events worldwide, Esposito shares his vision of software at software2cents@wordpress.com and on Twitter: @despos.
thanks to the following Microsoft technical expert for reviewing this article: Jovan Popovic
Cutting Edge
Figure 2 Results of a JSON Query 10 msdn magazine