Learn more about SQL Server tools

 

Tutorials          DBA          Dev          BI          Career          Categories          Events          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Events

DBA    Dev    BI    Categories

 

Introducing JSON for SQL Server 2016


By:   |   Read Comments (5)   |   Related Tips: More > 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:

Sample JSON Data

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 AUTO Output

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"}}]

FOR JSON AUTO Output

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.


Last Update:






About the author
MSSQLTips author Babu Akkandi 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.

View all my tips
Related Resources


 









Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


Get free SQL tips:

*Enter Code refresh code     



Friday, December 02, 2016 - 1:31:03 AM - ravindar Back To Top

 

 Great article! It is very useful information.  Thanks!!


Wednesday, June 08, 2016 - 4:12:53 PM - Harold Scott Back To Top

Great article! I am working on an executive overview that I need to present to my manager, can anybody point me to some other high-level discussions on this topic, I'm not a great writer (except when it comes to code :)) and I would like to see how some people describe it in a way that's easy for non-technical folks ... Thanks


Wednesday, September 09, 2015 - 3:33:34 AM - Babu Akkandi Back To Top

@Gourisankar, Thanks for your comments!

@kesavaram, Yes, you are right, We did Lot of workaround in .NET while converting the data to JSON Format. SQL 2016 made it with simple approach. Thanks for you comments!


Tuesday, September 08, 2015 - 3:48:34 AM - kesavaram s Back To Top

Very useful information, with the keyword JSON PATH being intorduced in sql server 2016 will reduce the time which puts in lot of efforts to develop a .NET utility.

Thanks Babu Akkandi.


Tuesday, September 08, 2015 - 3:32:01 AM - Gourisankar Back To Top

Nice Article, Thanks Babu ..!

Keep posting such valuable information.


Learn more about SQL Server tools