Native JSON (Java Script Object Notation) support is one of the new feature that is coming with Sql Server 2016.

Introduction to JSON

JSON is simple data representation format, which readable, portable and platform independent. Now JSON is a popular lightweight data-exchange format between the systems. Prior to JSON, web Services used XML as their primary data format for data-interchange. Now JSON is the preferred data-interchange format used by the Web APIs. To know more on JSON you can visit http://json.org/

JSON Anatomy

To know more on JSON you can visit http://json.org/

JSON In Sql Server 2016

JSON implementation in Sql server is on the similar lines as that of XML. One major difference is: JSON doesn’t have native JSON data type like the XML data type. We can store JSON data in regular NVARCHAR/VARCHAR column.

Below are the main JSON features introduced in Sql Server 2016. Click on the link to understand each of these features with an extensive list of examples.

FOR JSON Clause: can be used to convert the query result (rows) to JSON format.

OPENJSON Function : is very much similar to the OPENXML function, it provides a rowset view of the JSON. It is basically a table valued function, that can be used to transform the JSON value to one or many rows

ISJSON Function: This function validates whether the parameter supplied to it is a well formed JSON or not

JSON_VALUE Function: This function returns the scalar value from the input JSON text from the specified JSON path location

JSON_QUERY Function: This function extracts the JSON fragment (i.e. JSON object or an array) from the input JSON string from the specified JSON path.

JSON_MODIFY Function: This function can be used to update the value of the property in a JSON string and returns the updated JSON string.

Indexing Strategy for JSON Value in Sql Server 2016

lax and strict JSON Path modes in Sql Server 2016

[ALSO READ]: