Business Intelligence Developer (MCSE)

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:

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.

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.

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

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).

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

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.

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.

The query below show the ability to work with grouping operations

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

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.

Like most modern servers nowadays, Power BI has a fairly good set of web APIs that developers can leverage to extend functionalities of the product and further automate many processes. The developer’s section of the documentation provides an easy to follow set of instructions on how to get started and showcases some very useful feature … More Power BI REST API

Came across a few little extensions in Visual Studio Code marketplace recently that has and will further save me considerable time when I need to work with JSON. … More Serializing and Deserializing JSON

Whether you're a manager, developer or DBA, SQL Auditing is a powerful tool that can help you understand what is happening within your platform and its underlying data. Auditing on SQL Server allows you to monitor your server from two different aspects: at the server level and the database level. … More Auditing Database Servers with SQL Server-A Primer

In my last blog on partition tables, I talked about the benefits from an archiving and management perspective. In this one, I'll go over the query performance that can be gained. If partitioning is something new to you, it's worth thinking it as dividing large tables into smaller ones … More Partitioned for Performance

If you do any development beyond the most basic on the .Net Framework, understanding and resolving DLL dependencies is a necessity and a fact of life and without the right tools to guide you, this can be a very time consuming and frustrating exercise. … More Untangle and Understand DLL Dependencies with NDepend

It’s fairly common knowledge amongst database professionals partitioned tables can help considerably with performance in various situations but my guess is not many would have considered the enormous benefits when it comes to archiving, a necessity when it comes to massive tables. … More Horizontal Partitioning for Big Tables

Despite its reputation, I'm still a massive fan of linked servers. Microsoft developed it for a reason and it's ongoing presence on the platform since day one says it all. No one told you to pull back a gazillion rows on an inner join with a local table. So, when I saw it's now possible ... … More Linked Servers for Azure’s NoSQL Cosmos DB