Why Would I Want to Use JSON in SQL Server?

SQL Server 2016 offers built-in support for JSON, in a similar way to the support offered for XML since SQL Server 2000. Both XML and JSON are semi-structured data formats as opposed to the structured relational data format. SQL Server is an RDBMS, which stands for Relational Database Management System. It has a relational engine. So why XML and JSON?

Why would I want to use JSON or XML in my relational database?

Questions

Well, there are several use cases for which it makes sense. Let’s explore them…

  1. Store Semi-Structured Data

You already have a relational database with structured data, but some of the data in your application is semi-structured, and doesn’t map well to a relational structure.

One of our clients is running an online casino with multiple games, such as Blackjack, Roulette and Slot Machine. One of the requirements is to store a complete structure of each game in order to replay it later on the website. Modeling this data in a relational structure requires a new set of tables and relationships for each game type. It is much easier to store this data as either JSON or XML in a single column in the “GameResults” table. In each row of this table there will be a JSON/XML document with the relevant information corresponding to the game type and the specific game details represented by that row. For example, a document describing Blackjack will contain the set of cards of the dealer and the player, and whether the player used insurance or not. But for the Roulette the information within the document will be completely different, with the winning number and all the bets on the table.

By leveraging XML/JSON support in SQL Server, it is much easier to develop and maintain the database with this single column. We can now query the column using built-in methods and functions or simply read entire documents to be pared by the application.

  1. Facilitate Design and Development

Some data structures dynamically change all the time. Trying to constantly modify a relational schema can result in quite a lot of headache, not to mention downtime.

One of our clients develops a CRM application on top of SQL Server. There is a reporting module in the application, which allows users to customize and save their reports. Behind the scenes, the database needs to store the structure of each saved report as well as its data. Since users create new reports and modify existing reports all the time, storing all the data in a relational format is quite a challenge. It requires handling DDL statements, constraints, indexes and large data updates. Instead, we decided to store the structure of the reports as well as their data in XML format. By doing this, all the modifications are now performed by the application, and they are completely transparent to the database. This approach dramatically reduces design, development and deployment effort.

  1. Integration with NoSQL Platforms

You might have an architecture that involves several data platforms. Some of the data, such as financial transactions, is stored in a relational database, while other data, such as web logs, is stored in a NoSQL database, such as MongoDB.

Most NoSQL platforms use JSON as the native data format. When data from the relational database and data from the NoSQL database need to be correlated and manipulated, you need a way to transform one data format to the other. This is where JSON support in SQL Server becomes really handy. You can convert relational data to JSON by using the FOR JSON clause in a SELECT statement, and you can also convert JSON objects to relational format by using the OPENJSON function.

  1. Integration with Web Services and Applications

Similar to the integration with NoSQL platforms, many architectures involve web services and web applications, either internal or external, where the native protocol is based on XML. In such cases, there are occasions in which you need to extract relational data from an XML document and insert it into the relational database, or vice versa. Just like with JSON, you can use FOR XML and OPENXML to achieve these goals.

  1. T-SQL Tricks

This is not the main reason for using XML ort JSON in a relational database, but it can be handy in some cases. There are all kinds of creative ways to leverage XML/JSON functionality in order to achieve relational functionality. One such example is the use of FOR XML to perform a type of unpivot on a data set. I used this technique in my redundant indexes script, which identifies indexes that can be potentially dropped without a risk.

  1. Leverage Existing Skillset and Tooling

In today’s fast moving world, it is no longer appropriate to choose a single data platform for all of the data in your application. You should choose the right data platform for each data type depending on the structure, the usage, the volume, and more.