Introducing JSON for SQL Server 2016

Problem

For my current project, I must extract data stored in one database (D1) and load it into another database (D2). That should be simple, right? However, due to confidential data on the D2 database, the client is not ready to give us access. We have to push data to the D2 database via the REST API which accepts JSON as an input.

We are trying to find an option to convert the data into JSON format at the database level. Unfortunately, SQL Server does not support converting data into JSON format. To resolve this issue, we developed a .NET utility to convert the data to JSON format with the help of the “JavaScriptSerializer” class then pushed the data to the D2 database via REST API

I see SQL Server 2016 now supports JSON. Is it possible to replace our .NET utility? In this tip, we will demonstrate how to use JSON in SQL Server 2016.

Solution

JSON support in SQL Server is one of the highly ranked requests in Connect #673824, which received 1000+ votes.

What is JSON?

Stands for JavaScript Object Notation
Mainly stores and exchanges information
Well organized, easy-to-access, and in human-readable format of data that can be accessed in a logical manner
Self-describing and easy to understand
Lightweight data-interchange format
Language independent
Primarily transmits data between a server and web application, as an alternative to XML

Sample JSON Format

JSON data will look like the below example:

The sample JSON code snipped above is from https://json.org/example.

Why JSON in SQL Server?

Most of the major databases are adopting JSON support because the usage of JSON in Web APIs has increased significantly. Additionally, SQL Server 2016 enabled JSON support for the lingua franca* of web applications and to support direct querying to Hadoop. In SQL Server, JSON functionality is similar to XML Support.

*Lingua Franca: A Language that is adopted as a common language between speakers whose native languages are different. Referred from dictionary.reference.com/browse/lingua+franca

JSON Functionality in SQL Server

JSON features planned in SQL Server 2016:

SQL SERVER CTP2 – Format and export the data in JSON Format
SQL SERVER CTP3 – Extract and Load the JSON into Tables, seeking values from JSON text, index the column which has JSON data, etc.

CTP2 JSON Features

For JSON [AUTO | PATH] – We can get the JSON output by adding the “For JSON” clause in a SELECT statement. We can generate the JSON in two different modes, we will look at each option in the below sections.

JSON Functionality Expected in CTP3

Below details features expected to be available as part of CTP3:

Reference: http://blogs.msdn.com/b/jocapc/archive/2015/05/16/json-support-in-sql-server-2016.aspx

OPENJSON – OPENJSON is a table-value function, which accepts some text which includes a JSON value and returns rows in the required format. FOR JSON converts the rows into JSON format, where OPENJSON will convert back the JSON text into a table of rows.
IsJSON (< JSON text >) – This function verifies that the given text is formatted according to JSON standards/rules.
JSON_Value () – This is a scalar function. JSON_Value () parses the JSON text, then extracts a value if it exists in the path arguments. Similar to XPATH (XML Path), this functionality selects nodes from XML text. In addition, XPath may be used to compute values (e.g., strings, numbers, or Boolean values) from the content of an XML document.

JSON in SQL Server

To store JSON data in SQL Server, we have to use an NVARCHAR datatype. There is no separate datatype for JSON data.
NVARCHAR supports the storage of most of the data types and now extends to handle JSON.

Sample SQL with JSON

It is easy to pick up the FOR JSON syntax if you are familiar with the FOR XML clause in SQL Server as they are used similarly. JSON generates in 2 different modes: AUTO and PATH. Refer to the samples below.

FOR JSON AUTO – Automatically creates JSON objects based on the table used in the T-SQL statements. This functionality is similar to the FOR XML AUTO command.

Sample FOR JSON AUTO QUERY

USE AdventureWorks2014
GO
SELECT 
TOP 3 
        BusinessEntityID AS 'PersonID',
        FirstName AS 'FirstName', 
        MiddleName AS 'MiddleName', 
        LastName AS 'LastName'
FROM 
        [Person].[Person] 
FOR 
        JSON AUTO

SQL RESULT

[{"PersonID":285,"FirstName":"Syed","MiddleName":"E","LastName":"Abbas"},
{"PersonID":293,"FirstName":"Catherine","MiddleName":"R.","LastName":"Abel"},
{"PersonID":295,"FirstName":"Kim","LastName":"Abercrombie"}]

FOR JSON PATH – The JSON structure can be defined in the query by defining column alias with a dot (.) separated value (for example ‘Root.level1’). This Functionality is similar to FOR XML PATH.

Sample FOR JSON PATH QUERY

USE AdventureWorks2014
GO
SELECT 
    TOP 3
       BusinessEntityID AS 'Persons.PersonID',
       FirstName AS 'Persons.FirstName',
       MiddleName AS 'Persons.MiddleName',
       LastName AS 'Persons.LastName'
FROM
       [Person].[Person]
FOR
       JSON PATH

SQL RESULT

[{"Persons":{"PersonID":285,"FirstName":"Syed","MiddleName":"E","LastName":"Abbas"}},
{"Persons":{"PersonID":293,"FirstName":"Catherine","MiddleName":"R.","LastName":"Abel"}},
{"Persons":{"PersonID":295,"FirstName":"Kim","LastName":"Abercrombie"}}]

Next Steps

With JSON support available in SQL Server 2016 begin to start, learn more about the technology, and consider opportunities to implement it in your environment.

Resources

Community

Subscribe