SQL SERVER – Getting Started and Creating JSON Using SQL Server 2016 – JavaScript Object Notation

With SQL Server 2016 CTP out, I know many have started to play around with it and exploring some of the new capabilities. One of the new capabilities that has caught my attention is the introduction of JSON support. In this blog, we will get started in creating a JSON document using our SQL Server instance. Let me first introduce what JSON (JavaScript Object Notation) is and then we will move on.

SQL Server 2016 will support JSON (JavaScript Object Notation). JSON is an open, text-based exchange format based on JavaScript’s object literal notation. JSON is a popular data-interchange format used in modern web and mobile applications, as well for storing unstructured data. It is supported in several DB/NoSQL engines and this improvement will enable developers to put some JSON processing logic in the SQL Server that will enable them to parse, query, analyze, and update JSON data.

In this blog we will query a standard table and then convert it into JSON format. For this example, we are querying the standard AdventureWorks database:

SELECT TOP(2) PP.[PersonType], PP.[FirstName], PP.[MiddleName], 
PP.[LastName], EA.[EmailAddress]
FROM Person.Person PP
INNER JOIN [Person].[EmailAddress] EA
ON EA.[BusinessEntityID] = PP.[BusinessEntityID]

Sample output for rows inside SQL Server Management Studio looks like above. Now we will add the FOR JSON AUTO support to get the same output in JSON format.

SELECT TOP(2) PP.[PersonType], PP.[FirstName], PP.[MiddleName], 
PP.[LastName], EA.[EmailAddress]
FROM Person.Person PP
INNER JOIN [Person].[EmailAddress] EA
ON EA.[BusinessEntityID] = PP.[BusinessEntityID]
FOR JSON AUTO

[
{
“PersonType”:”EM”,
“FirstName”:”Ken”,
“MiddleName”:”J”,
“LastName”:”Sánchez”,
“EA”:[{“EmailAddress”:”ken0@adventure-works.com”}] },
{“PersonType”:”EM”,
“FirstName”:”Terri”,
“MiddleName”:”Lee”,
“LastName”:”Duffy”,
“EA”:[{“EmailAddress”:”terri0@adventure-works.com”}] }
]

I will write about writing nested queries for a different time, but we can also generate JSON using the PATH option like:

SELECT TOP(2) PP.[PersonType], PP.[FirstName], PP.[MiddleName], 
PP.[LastName], EA.[EmailAddress]
FROM Person.Person PP
INNER JOIN [Person].[EmailAddress] EA
ON EA.[BusinessEntityID] = PP.[BusinessEntityID]
FOR JSON PATH

The output for this query would look little different like:
[
{“PersonType”:”EM”, “FirstName”:”Ken”, “MiddleName”:”J”, “LastName”:”Sánchez”, “EmailAddress”:”ken0@adventure-works.com”},{“PersonType”:”EM”, “FirstName”:”Terri”, “MiddleName”:”Lee”, “LastName”:”Duffy”, “EmailAddress”:”terri0@adventure-works.com”}
]

Now that we are able to generate few simple output using JSON keyword, we will blog about other options in future blogs.

I would be interested to know if you will ever use JSON in your environment inside SQL Server? What would be your usecase for the same? Let me know and I would be more than happy to blog some of them here.

Reference: Pinal Dave (https://blog.sqlauthority.com)