JSON Compatibility

Well everyone SQL 2016 is now available to the public and with it comes fucntionality that everyone has been asking and waiting for, JSON support! With today’s world utilizing more JSON for it’s data interchange instead of just XML, the requests for native JSON support in DBMSs have been increasing. SQL Server answered those developers that were requesting this new support and has provided local JSON functions and full native functionality for handling JSON items. Before we jump into the new JSON functionality lets review what JSON is and why it’s good.

What is JSON?

JavaScript Object Notation (JSON) is a way to store information in an easy-to-read, easy-to-access manner. It essentially gives us a more human-readable format of the collected data that can be accessed in a highly logical way. With more and more websites using AJAX in their back end it’s becoming much more important for websites to be able to load data very quick and asynchronously. I say asynchronously because it runs the request for data in the background as to not cause page deadlocks.

These AJAX powered websites all provide what are known as RSS feeds, which are very easy to import and use on the server-side but when loading with AJAX you run into an error: you can only load an RSS feed if you are requesting it from the same domain that it’s hosted on. JSON uses a method called “JSONP” that uses a callback function to push the JSON data back to whichever domain you’re on, thus allowing you to easily store the JSON data and call it whenever you need it.

For a quick reference into what JSON actually looks like see below:

Example

First let’s create a table that will store some test data about a few of our favorite super heroes.

CREATE TABLE [dbo].[People]
(
[Id] [INT] NULL ,
[FirstName] [VARCHAR](50) NULL ,
[LastName] [VARCHAR](50) NULL ,
[Address] [VARCHAR](50) NULL ,
[City] [VARCHAR](50) NULL ,
[State] [VARCHAR](50) NULL ,
[Zip] [VARCHAR](50) NULL 
)
ON [PRIMARY];
GO

Let’s add in our super heroes.

INSERT INTO dbo.People
( Id ,
FirstName ,
LastName ,
Address ,
City ,
State ,
Zip
)
VALUES ( 1 ,
‘Captain’ ,
‘America’ ,
‘1234 Freedom Street’ ,
‘Ann Arbor’ ,
‘MI’ ,
‘48108’
),
( 2 ,
‘Iron’ ,
‘Man’ ,
‘5678 Genius Ave.’ ,
‘Chelsea’ ,
‘MI’ ,
‘48118’
),
( 3 ,
‘Black’ ,
‘Widow’ ,
‘910 Russian Lane’ ,
‘Detroit’ ,
‘MI’ ,
‘48108’
)

Now that we have a table filled with data let’s pull all of it out and convert to a JSON item. For this example let’s simply grab everything from the table and return it using JSON AUTO.

SELECT *
FROM dbo.People
FOR JSON AUTO

You will notice in the above function that there is a section labeled “FOR JSON AUTO” and I know exactly what you’re thinking, it looks exactly like how you have to tell SQL to return XML data type. Of course Microsoft would fit JSON in the same way, it makes total sense. Using FOR JSON AUTO works the same way as FOR XML AUTO and will simply return the JSON format of the data you request. The result returned from SQL is below as well as the formatted version of the JSON.

SQL:

Formatted JSON:

[
{
“Id”:1,
“FirstName”:”Captain”,
“LastName”:”America”,
“Address”:”1234 Freedom Street”,
“City”:”Ann Arbor”,
“State”:”MI”,
“Zip”:”48108″
},
{
“Id”:2,
“FirstName”:”Iron”,
“LastName”:”Man”,
“Address”:”5678 Genius Ave.”,
“City”:”Chelsea”,
“State”:”MI”,
“Zip”:”48118″
},
{
“Id”:3,
“FirstName”:”Black”,
“LastName”:”Widow”,
“Address”:”910 Russian Lane”,
“City”:”Detroit”,
“State”:”MI”,
“Zip”:”48108″
}
]

We must keep in mind that this is just JSON support and not the native JSON type. In SQL Server 2016 the JSON data will be represented as the NVARCHAR data type. But what happens if we want to reverse the process? Take a JSON object and then transform it into a relational table? Microsoft built in conversion for this as well! Let’s use the previous result as the base for the transformation from JSON object to relational table.

DECLARE @JSON NVARCHAR(MAX)=(
SELECT Id ,
FirstName ,
LastName ,
Address ,
City ,
State ,
Zip
FROM dbo.People
FOR JSON AUTO)

SELECT Array.Id ,
Array.FirstName ,
Array.LastName ,
Array.Address ,
Array.City ,
Array.State ,
Array.Zip
FROM OPENJSON(@JSON,’$’)
WITH (Id INT,
FirstName VARCHAR(50) ,
LastName VARCHAR(50) ,
Address VARCHAR(50) ,
City VARCHAR(50) ,
State VARCHAR(50) ,
Zip VARCHAR(50)
) AS Array

Much like XML paths you are able to reference specific paths in JSON objects. The dollar sign ($) represents the input JSON object and is using a JavaScript-like syntax for referencing properties within JSON text. A few examples include:

• ‘$’ – references the entire JSON object
• ‘$.property1’ – references property1 in the JSON object
• ‘$[5]’ – references the 5th element in the JSON array

Other JSON Functions

• ISJSON() – this checks if the NVARCHAR text input is properly formatted to JSON specifications
• JSON_VALUE() – parses through the JSON text to extract scalar values on the path