SQL Server 2016’s JSON Integration

While the big theme for the release of SQL Server 2016 is embracing the cloud and cloud services, one of the most easily overlooked new features in SQL Server 2016 is its integrated support for JSON (JavaScript Object Notation). JSON is a lightweight data-interchange format that’s based on a subset of the JavaScript Programming Language. It is human readable and easy for computers to parse and generate, which makes JSON a popular data format for exchanging data in today’s cloud, Web and mobile applications. JSON is also used for storing unstructured data in log files or NoSQL databases like Hadoop, Couchbase Server and Microsoft Azure DocumentDB. Many REST Web services return results formatted as JSON text and accept input data formatted as JSON.

Basic JSON Structure

If you’re not familiar with JSON, it’s important to realize that JSON is built on two basic structures: a collection of name/value pairs and an ordered list of values. You can see an example of JSON’s name/value structure in the following example.

{    "id": 1,    "name": "A green door",     "price": 12.50,    "tags": ["home", "green"]}

This text-based structure easily lends itself to cross-system data interchanges.

SQL Server 2016 JSON Support

Before SQL Server 2016, you had to roll your own support for JSON if you wanted to incorporate it with SQL Server. SQL Server 2016 added integrated JSON support, with the ability to format and export data as JSON; load JSON text in tables; extract values from JSON text; and index properties in JSON text. 

SQL Server 2016’s support for JSON is not like its XML support, which has a native data type. JSON, instead, is implemented as an NVARCHAR. This eliminates any need to change database schema and reload data in order to use the new JSON integration. Using NVARCHAR also means that JSON is supported by all SQL Server components that support the NVARCHAR type--which essentially means that JSON will be supported everywhere. This includes In-Memory OLTP, the Columnstore Index, SSIS and SSAS. Even the new security features like Always Encrypted, Row-level Security and Transparent Data Encryption will support JSON.

Integrating Relational Data and JSON

SQL Server 2016 enables you to export relational data as JSON for easy consumption into Web and cloud services, as well as to convert JSON data into relational data. You can see an overview of SQL Server 2016’s JSON support in Figure 1.

Figure 1 – SQL Server 2016’s JSON Integration

In order to export relational data as JSON, you need to use the new FOR JSON clause with your T-SQL SELECT statements. Adding the FOR JSON clause to a T-SQL SELECT query will format the query results as JSON and return that JSON to client. Every row in the result set will be formatted as one JSON object.

To transform JSON text into relational data, Microsoft added the OPENJSON function. OPENJSON is table-value function (TVF) that inputs some JSON text, and for each element generates one row of relational data in the output result. OPENJSON provides a rowset view over a JSON document, with the ability to specify the columns in the rowset and the property paths to populate the columns. You can use the results of the OPENJSON function in the T-SQL FROM clause like any other set of rows.

JSON Functions                                           

SQL Server 2016 also provides several built-in functions for working with JSON data:

HPE and Microsoft are the underwriters of this article.