{"id":4014,"date":"2015-10-25T18:46:40","date_gmt":"2015-10-25T13:16:40","guid":{"rendered":"http:\/\/sqlhints.com\/?p=4014"},"modified":"2016-03-18T22:26:20","modified_gmt":"2016-03-18T16:56:20","slug":"json-in-sql-server-2016","status":"publish","type":"post","link":"https:\/\/sqlhints.com\/2015\/10\/25\/json-in-sql-server-2016\/","title":{"rendered":"Native JSON Support in Sql Server 2016"},"content":{"rendered":"

Native JSON (Java Script Object Notation) support is one of the new feature that is coming with Sql Server 2016.<\/p>\n

Introduction to JSON<\/h3>\n

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\/<\/a><\/p>\n

JSON Anatomy<\/strong>
\n\"JSON<\/a><\/p>\n

  • JSON Notations<\/strong>
    \n” <\/strong> : JSON Property Name and value (if it is string) is enclosed within double quotes
    \n: <\/strong> : Colon symbol is used as the separator between the JSON property Name and Value.
    \n{} <\/strong> : Curly braces symbol represents the objects
    \n[,] <\/strong> : Arrays of Value
    \n[{},{}] <\/strong> : Arrays of Objects<\/li>\n

    To know more on JSON you can visit http:\/\/json.org\/<\/a><\/p>\n

    JSON In Sql Server 2016<\/h3>\n

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

    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.<\/p>\n

    FOR JSON Clause<\/strong>: can be used to convert the query result (rows) to JSON format.<\/a><\/p>\n

    OPENJSON Function<\/strong> : 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<\/a><\/p>\n

    ISJSON <\/strong> Function: This function validates whether the parameter supplied to it is a well formed JSON or not<\/a><\/p>\n

    JSON_VALUE <\/strong> Function: This function returns the scalar value from the input JSON text from the specified JSON path location<\/a><\/p>\n

    JSON_QUERY<\/strong> Function: This function extracts the JSON fragment (i.e. JSON object or an array) from the input JSON string from the specified JSON path.<\/a><\/p>\n

    JSON_MODIFY<\/strong> Function: This function can be used to update the value of the property in a JSON string and returns the updated JSON string.<\/a><\/p>\n

    Indexing Strategy<\/strong> for JSON Value in Sql Server 2016<\/a><\/p>\n

    lax<\/strong> and strict<\/strong> JSON Path modes in Sql Server 2016<\/a><\/p>\n

    [ALSO READ]:<\/strong><\/p>\n\n\n\n\n
    New features in SQL SERVER 2016<\/strong><\/td>\n<\/tr>\n
    \n