NEW T-SQL FEATURES IN SQL SERVER 2016 – XIV | JSON Support in SQL Server 2016

In this post we shall check out the new features Microsoft introduced in SQL Server 2016 | JSON Data (SQL Server)

New feature – XIV | JSON Support in SQL Server 2016


JSON full form is JavaScript Object Notation and it is used for storing and exchanging data. It is a popular textual data format used for exchanging data in modern web and mobile applications. JSON is also used for storing unstructured data in log files or NoSQL databases like Microsoft Azure Cosmos DB.

Notes

1. In SQL Server 2016 JSON will be represented as NVARCHAR type.
2. Example of valid JSON

--
	'{
		 "name":"Pawan"
		,"MiddleName":"Kumar"
		,"Surname":"Khowal"
		,"Class":10
		,"SkillSet":["SQL","SSIS","SSRS","SSAS"]
		,"IsDatabaseDeveloper":true
	}';
--

3. SQL Server provides built-in functions and operators that let you do the following things with JSON text.

3.a Parse JSON text and read or modify values.
3.b Transform arrays of JSON objects into table format.
3.c Run any Transact-SQL query on the converted JSON objects.
3.d Format the results of Transact-SQL queries in JSON format.

JSON built-in functions

If you have JSON text that’s stored in database tables, you can use built-in functions to read or modify values in the JSON text.

FunctionName Description URL
JSON_VALUE Used to extract a scalar value from a JSON string Will be added very soon..
JSON_QUERY Used to extract an object or an array from a JSON string. Will be added very soon..
ISJSON Used to check whether a string contains valid JSON or not. http://bit.ly/2DxaEu5
JSON_MODIFY Used to modify a value in a JSON string. Will be added very soon..
OPENJSON Used to converts JSON text into a set of rows and columns. http://bit.ly/2DoiJh7

Refer Microsoft LINK below for more details-

1. https://docs.microsoft.com/en-us/sql/relational-databases/json/json-data-sql-server
2. https://blogs.msdn.microsoft.com/sqlserverstorageengine/tag/json/
3. https://blogs.msdn.microsoft.com/jocapc/2015/05/16/json-support-in-sql-server-2016/

Enjoy !!! Keep Learning

Pawan Khowal 

Http://MSBISkills.com

Advertisements