SQL with Manoj

In my [previous blog post] on “SQL Server 2016 Enhancements” we discussed about the new features coming in. Native JSON support is on of those feature that will help you support and manage NoSQL/Unstructured document data within a SQL Server database.
 

–> JSON (or JavaScript Object Notation) is a popular, language-independent, lightweight data-interchange format used in modern web and mobile applications, as well for storing Unstructured data. JSON is an alternate to XML and is more compact than that format, and thus has become the first choice for many applications that need to move data around on the web. One of the biggest reasons JSON is becoming more important than XML is that XML has to be parsed with an XML parser, while JSON can be parsed by a standard JavaScript function. This makes it easier and faster than working with XML.

For more details on JSON check at json.org

–> JSON in SQL Server 2016 is not treated as a separate Datatype by the DB Engine, like XML. For example, appending FOR JSON AUTO to a standard SELECT statement returns the result set in a JSON format. The JSON data is stored as NVARCHAR type, unlike XML datatype for XML data, thus JSON will be supported wherever NVARCHAR is supported.

–> Here is a sample & simple query to convert a row-set into a JSON format:

SELECT TOP 10 
	M.ProductModelID, 
	M.Name AS [ProductModel.Name],
	ProductID, 
	P.Name AS [Product.Name], 
	ProductNumber, 
	MakeFlag
FROM Production.Product P
INNER JOIN Production.ProductModel M 
ON P.ProductModelID = M.ProductModelID 
FOR JSON PATH, ROOT('ProductModel') -- here, JSON syntax similar to XML

Check the last line, the syntax is almost similar to XML datatype. So, if you familiar with XML syntax in T-SQL, working with JSON will be a seamless experience.
 

Check my [next post] on how to work with JSON string with SQL queries.
 

Check this video on how to work with JSON and SQL:

–> More from MS BoL and whitepapers on JSON support:

JSON syntax is simple and human-readable. JSON values consist of name/value pairs, and individual values are separated by commas. Objects are containers of one or more name/value pairs and are contained within curly brackets. JSON arrays can contain multiple objects, and arrays are contained within square brackets.

JSON is the storage format used in several NoSQL engines, including Azure DocumentDB. DocumentDB uses Azure Blob storage to store schema-less documents, but provides a rich SQL query dialect that allows you to conduct SQL queries over the data contained in the documents. In addition to DocumentDB, Azure Search also utilizes JSON. Azure Search is a fully managed search solution that allows developers to embed sophisticated search experiences into web and mobile applications without having to worry about the complexities of full-text search and without having to deploy, maintain, or manage any infrastructure.

The combination of SQL Server’s new support for JSON with these other Microsoft tools enables many scenarios for moving data back and forth between relational and schema-less storage and the applications that access such data. For example, these tools would allow you to set up periodical extractions of relational data in SQL Server, transforming the data into JSON and loading it to a JSON-based Azure DocumentDB storage that is searchable from a mobile device, along with data from many other sources, utilizing Azure Search.
 

Check more on [MSDN BoL] for JSON support in SQL Server 2016.