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)
5 Comments. Leave new
Dave,
I’ve been working in a slightly more legacy environment that utilizes SQL Server 2008 R2. I’ve seen, and attempted to move us towards a more web-based methodology utilizing the CRUD app structure. Since I’m newer to coding, I’ve been working mostly in JavaScript. I found an application called NodeJS ) which I’m sure you’ve heard of, but I’ll post the link there for any interested. Once I’ve got NodeJS installed, I use npm (node package manager) to install the node-mssql package. (
NOTE: There are several different mssql’s and add ins to use. I prefer to use the base package created by PatrikSimek. ) Also, if you’re new to npm, in command line you can use the command “npm install -g” to install a package globally. This automatically adds the bin folder processes to your command line, as if you had added them to your PATH variable.
Once, I’ve done this, I can create a plain JavaScript file, and use “require” to include all kinds of awesome packages built up for this tool, including if I wanted using Express ) middleware to service out any http requests. I can return a JSON formatted response (from a SQL server instance, given a SQL Server query) directly to the requesting client and it is automatically formatted to JSON.
The node package “mssql” can connect to sql instances itself and execute processes and return result sets if you had automated processes that needed to occur. However, the addition of Express allows me the opportunity to service out requests to my SQL Server instances automatically, and also has some management code to prevent JavaScript, or SQL injection attacks. This makes this lightweight, fairly simple to understand package one of the most valuable SQL tools in all of NodeJS’s arsenal in my opinion.
Not covered in this post is “StrongLoop” which is a very robust tool you can find at the expressJS link mentioned prior. This tool has a multi-sql connection capability. Meaning, I can set the tool up to connect to Oracle, SQL Server, MongoDB, etc all from one code set. I haven’t integrated Strongloop yet, but I plan to connect that into our projects in the future. Cheers Dave thanks for always having great tips and tricks to share!
Hi Pinal,
We are even using JSON for storing data in SQL Server 2012 under varchar(max) datatype. we have a case where we port data from third party and there fields count can be changed. so we query it as normal data and then parse it on server side. But here you are talking about the output as JSON that is also helpful where we have to push data through APIs. Please let me know if we can save data as JSON datatype and it will allow us to filter data accordingly at SQL server level
Cheers!
Aman
Hi Pinal,
Great info as always. Our potential use-case is continuing to use our very robust custom TSQL-based ETL solution but then exporting JSON for import into MongoDB for web consumption. I’ve created some code that works on our SQL 2008 R2 server, but it’s slower than I’d like at 30 minutes for ~1M records. It’s essentially just doing a bunch of string manipulation with no concept of JSON. Clearly I’ll have to get my hands on 2016 so I do some speed tests and start making the case for those per-virtual-core licenses!
Thanks,
Russell
What will JSON PATH return if there is more than one related email address?
Dave, how to export data in JSON file from SQL Server? Like a huge table with few million of records.