Page 13 - MSDN Magazine, April 2017

You can use the function to insert or update regular table rows from plain JSON text. The WITH clause lets you map JSON prop- erties to existing table columns.
The Event Sourcing Scenario
In my December 2016 column, I discussed Event Sourcing as an emerging pattern to store the historical state of the application (msdn.com/magazine/mt790196). Instead of saving the latest-known good state, with Event Sourcing you save every single business event that alters the state and rebuild the latest state replaying the past events.
With JSON, features enabled using SQL Server in an Event Sourcing scenario become realistic.
The crucial aspect of an Event Sourcing implementation is how effectively you can save and retrieve the past events. Every event is different and might have a different schema, depending on the type and information available. At the same time, having a distinct (relational) store for each event type is problematic because events come asynchronously and might affect different entities and dif- ferent segments of the state. If you keep them in different tables, rebuilding the state might become expensive because of cross-table JOINs. Hence, saving events as objects is the most recommended option and NoSQL stores do the work very well. Is it possible to do Event Sourcing with a relational database instead?
Saving the event as JSON is an option possible on any version of SQL Server, but reading JSON effectively, when large numbers of events are in store, might be unsustainable. With the native JSON features in SQL Server 2016, the landscape changes and using SQL
Server in an Event Sourcing scenario becomes realistic. However, how would you query JSON from a database table?
Querying Data Out of JSON Content
So let’s say you managed to have one or more columns of JSON data in a canonical relational table. Therefore, columns with primitive data and columns filled with JSON data live side by side. Unless the new functions of SQL Server 2016 are used, the JSON columns are treated as plain text fields and can be queried only with T-SQL string and text instructions such as LIKE, SUBSTRING and TRIM. For the purpose of the demo, I built a column called Countries—with a few tabular columns—and another named Serialized that contains the entire rest of the record serialized as JSON, as shown in Figure 1.
The JSON object serialized in the sample table looks like this:
{
"CountryCode":"AD", "CountryName":"Andorra", "CurrencyCode":"EUR", "Population":"84000", "Capital":"Andorra la Vella", "ContinentName":"Europe", "Continent":"EU", "AreaInSqKm":"468.0", "Languages":"ca", "GeonameId":"3041565", "Cargo":null
The following T-SQL query shows how to select only the coun- tries that count more than 100 million inhabitants. The query mixes regular table columns and JSON properties:
SELECT CountryCode,
CountryName,
JSON_VALUE(Serialized, '$.Population') AS People
FROM Countries
WHERE ISJSON(Serialized) > 0 AND
JSON_VALUE(Serialized, '$.Population') > 100000000 ORDER BY JSON_VALUE(Serialized, '$.AreaInSqKm')
The JSON_VALUE function takes the name of a JSON column (or a local variable set to a JSON string) and extracts the scalar value following the specified path. As shown in Figure 2, the $ symbol refers to the root of the serialized JSON object.
Because the JSON column is configured as a plain NVARCHAR column, you might want to use the ISJSON function to check whether the content of the column is real JSON. The function returns a pos-
itive value if the content is JSON. JSON_VALUE always returns a string of up to 4,000 bytes, regard- less of the selected property. If you expect a longer return value, then you should use OPENJSON instead. At any rate, you might want to consider a CAST to get a value of the proper type. Looking back at the previous example, let’s say you want the number of people living in a country formatted with com- mas. (In general, this might not be a good idea because formatting data in the presentation layer gives your code a lot more flexibility.) The
April 2017 9
Figure 1 The Sample Countries Database with a JSON Column msdnmagazine.com