Introducing JSON for SQL Server 2016

By: Babu Akkandi   |   Comments (7)   |   Related: > SQL Server 2016

Problem

On my current project, there is a requirement to extract data, which is stored in one database (D1) and load it into another database (D2). That should be simple, right? But, the client is not ready to give access to the D2 database due to confidential data. 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 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?

• JSON stands for JavaScript Object Notation
• JSON is mainly used to store and exchange information
• JSON data is well organized, easy-to-access and in human-readable format of data that can be accessed in a logical manner
• JSON is self-describing and easy to understand
• JSON is a lightweight data-interchange format
• JSON is language independent
• JSON is used primarily to transmit 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 http://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. SQL Server 2016 also enabled JSON support for the lingua franca* of web applications in addition to supporting direct querying to Hadoop. JSON functionality in SQL Server 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 that are 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.

JSON Features in CTP2

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

The features listed below are 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, but OPENJSON will convert back the JSON text into a table of rows.
• IsJSON (< JSON text >) - This function verifies, whether the given text is formatted according to JSON standards/rules.
• JSON_Value () - This is a scalar function and it parses the JSON text then extracts a value if it exists in the path arguments. This functionality is similar to XPATH (XML Path) which is used for selecting 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 because there is no separate datatype for JSON data.
• NVARCHAR supports the storage of most of the data types and now is extended to handle JSON as well.

Sample SQL with JSON

If you are familiar with the FOR XML clause in SQL Server, then you can pick up the FOR JSON syntax very easily because it can be used in the similar way. JSON can be generated in 2 different modes (AUTO and PATH). Refer to the samples below.

FOR JSON AUTO – JSON objects are created automatically 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 – We can define the JSON structure 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 learning more about the technology and consider opportunities to implement it in your environment.

About the author

Babu Akkandi is a BI Professional and has 11+ years of experience in the IT industry and has been working with SQL Server since version 7.0.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips