JSON with SQL 2016

When I first read the reviews for SQL 2016 over a year ago just before its official release, one of the things that got me excited was JSON was finally coming to SQL Server. I wasn’t alone, the reviews were good and it was after all one of the most highly ranked requests on the Microsoft Connect site. This was partly because apart from becoming ever more common, JSON is also perceived to be easier to use or at the least less verbose than XML, it’s earlier alternative. Personally, I think they’re about the same but having it on SQL Server could definitely save us a lot of time. Even Oracle by this stage was in on the game and offering something similar if not more powerful with Oracle 12.1.

JSON stands for JavaScript Object Notation and is an open standard file format that uses human-readable text to transmit data objects consisting of attribute-value pairs and array data types. Put simply, it is used to facilitate data transfer in a platform independent format and an alternative to XML though in my observation, far more pervasive and finally living up to the expectations set for XML many years ago-it is really everywhere though for the present time being, you’ll most likely see it on the receiving end of a REST API call or NoSQL Document DB. You probably come across it frequently without noticing it such as on your web browser-if you open this page, you’ll see a normal web page but behind the scenes JSON is sent by the web server and consumed by the same web page by the JavaScript. IF you want to see just the raw JSON, just call the same API within that page (compare the URL of the two pages). There are more examples here for beginners: OData_REST

Much has already been written about it, so I’ll cover the basics and provide a high overview. The official document  by Microsoft is probably all you’ll need as far was working with SQL Server, it covers pretty much all the nuances.

 Structure

Like XML, JSON can seem a bit complex at first. A great place to start if you come across a massive or complex JSON file (one that is deeply nested) is to use sites like https://jsonlint.com/  to validate and format your JSON to make it easier to read. It turns something like this:

{“Customer 1”:[{“Customer”:{“Id”:1,”Name”:”Customer NRZBB”,”Order”:{“Id”:10692,”Date”:”2015-10-03″}}}]}

into to something like this:

{
        { "Customer 1": [{ 
                "Customer": { 
                     "Id": 1, 
                      "Name": "Customer NRZBB", 
                       "Order": { 
                                 "Id": 10692, 
                                 "Date": "2015-10-03" 
                                } 
                            }
                     }]
}

You could do the same thing in Visual Studio but many BI/SQL developers and DBAs don’t use the full blown Visual Studio that’s required.

Usage 

Like XML before it, there’s basically three main use cases for JSON in SQL Server. These are, creation, storage and query.

  1. Creating JSON

This is a massive time saver for certain situations such as producing the JSON file from your DB without the need to whip up some object-oriented language like C# or Java. You simply write SQL against the tables and it’ll output the JSON similarly to how XML is produced.

J1

From the output above, click the link and it’ll take you to the JSON collection/object.

j2

  1. Storing JSON

Storing JSON within SQL Server is exactly the same as XML, you can store it in any table structure as long as data type is NVARCHAR(MAX).

Capture

This query below illustrate the alternate approach by shredding the JSON string and persisting this to disk.

j6

  1. Querying JSON

There’s a number of way your can query the JSON, I’ll quickly how you the more interesting ones. This one simply reads the JSON object stored as a string and outputs the results into a grid. Notice nested objects like Orders here are displayed within the same column.

j3

With the results below, the same one row is displayed differently depending on your query. Notice each object is returned in a different row for the same JSON collection/string in the second query.

j4

The query below show the ability to work with grouping operations

Capture

And finally this shows the equivalent in C# with LINQ queries if done outside SQL Server. Notice the similarities.

j6

A number of people have commented the JSON offering in SQL Server is not as complete its as XML or its competitors like Oracle in the same space. That is certainly true and we can only hope Microsoft does something soon to remedy this situation though for version one, can’t complain too much.

JSON, if you work with data, expect to see more of it.

 


One thought on “JSON with SQL 2016

Leave a Reply