{"id":4160,"date":"2015-11-22T10:41:02","date_gmt":"2015-11-22T05:11:02","guid":{"rendered":"http:\/\/sqlhints.com\/?p=4160"},"modified":"2015-12-23T07:18:55","modified_gmt":"2015-12-23T01:48:55","slug":"openjson-function-in-sql-server-2016","status":"publish","type":"post","link":"https:\/\/sqlhints.com\/2015\/11\/22\/openjson-function-in-sql-server-2016\/","title":{"rendered":"OPENJSON Function in Sql Server 2016"},"content":{"rendered":"<p style=\"text-align: justify;\"><strong>OPENJSON<\/strong> is one of the new JSON function introduced in Sql Server 2016, it is very much similar to the OPENXML function. This table valued function can be used to transform the JSON text to one or many rows. Basically, it provides a row-set view of the input JSON string.<\/p>\n<p><strong>Syntax:<\/strong><\/p>\n<pre class=\"brush: sql; gutter: false\">\r\nOPENJSON( json_string [, json_path ] )\r\n[ WITH (column_mapping_definition1\r\n        [,column_mapping_definition2] \r\n        [,\u2026 column_mapping_definitionN])\r\n]<\/pre>\n<p style=\"text-align: justify;\"><strong>Where:<\/strong> column_mapping_definition :: column_name  column_type [ column_json_path ] [ AS JSON ]<\/P><\/p>\n<p style=\"text-align: justify;\"><strong>json_string <\/strong>is the JSON string from which will be transformed to row(s).<\/P><\/p>\n<p style=\"text-align: justify;\"><strong>json_path<\/strong> is the location of the JSON string in the json_string parameter, which will converted to one or many rows.<\/P> <\/p>\n<p style=\"text-align: justify;\"><strong>WITH <\/strong>clause: This is an optional clause it can used to explicitly specify the schema of the result. Each column in the resultant output can be defined by specifying the column name, column type, it\u2019s location i.e. column json path and the [AS JSON] clause specifies that the column value will be a JSON object or an array, if it is not specified the expected column value is a scalar value.<\/P><br \/>\n<strong>[ALSO READ]:<\/strong><\/p>\n<ul>\n<li><a href=\"https:\/\/sqlhints.com\/2015\/10\/25\/json-in-sql-server-2016\/\" target=\"_blank\">Native JSON Support in Sql Server 2016<\/a><\/li>\n<li><a href=\"https:\/\/sqlhints.com\/2015\/11\/15\/for-json-clause-in-sql-server-2016\/\" target=\"_blank\">FOR JSON Clause in Sql Server 2016<\/a><\/li>\n<li><a href=\"https:\/\/sqlhints.com\/2015\/11\/15\/isjson-function-in-sql-server-2016\/\" target=\"_blank\">ISJSON Function in Sql Server 2016<\/a><\/li>\n<li><a href=\"https:\/\/sqlhints.com\/2015\/11\/15\/json_value-function-in-sql-server-2016\/\" target=\"_blank\">JSON_VALUE Function in Sql Server 2016<\/a><\/li>\n<li><a href=\"https:\/\/sqlhints.com\/2015\/11\/15\/json_query-function-in-sql-server-2016\/\" target=\"_blank\">JSON_QUERY Function in Sql Server 2016<\/a><\/li>\n<li><a href=\"https:\/\/sqlhints.com\/2015\/11\/15\/lax-and-strict-json-path-modes-in-sql-server-2016\/\" target=\"_blank\">lax and strict JSON Path modes in Sql Server 2016<\/a><\/li>\n<li><a href=\"https:\/\/sqlhints.com\/2015\/11\/28\/indexing-strategy-for-json-value-in-sql-server-2016\/\" target=\"_blank\">Indexing Strategy for JSON Value in Sql Server 2016<\/a><\/li>\n<\/ul>\n<p style=\"text-align: justify;\">Let us understand this OPENJSON function with an extensive list of examples.<\/p>\n<h3>OPENJSON examples without explicit schema declaration<\/h3>\n<p><strong>Example 1:<\/strong> Basic OPENJSON example with default schema<\/p>\n<pre class=\"brush: sql; gutter: false\">\r\nSELECT *\r\nFROM OPENJSON(&#039;{&quot;FirstName&quot;:&quot;Basavaraj&quot;,&quot;LastName&quot;:&quot;Biradar&quot;}&#039;)\r\n<\/pre>\n<p><strong>RESULT:<\/strong><br \/>\n<a href=\"https:\/\/sqlhints.com\/wp-content\/uploads\/2015\/11\/OPENJSON-with-out-schema-declaration-Example-1.jpg\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/sqlhints.com\/wp-content\/uploads\/2015\/11\/OPENJSON-with-out-schema-declaration-Example-1.jpg\" alt=\"OPENJSON with out schema declaration Example 1\" width=\"500\" height=\"190\" class=\"alignnone size-full wp-image-4162\" \/><\/a><\/p>\n<p style=\"text-align: justify;\">From the result we can observe that when OPENJSON function is executed with the default schema (i.e. Without explicit schema declarartion), following three columns are returned in the result.<\/p>\n<p style=\"text-align: justify;\"><strong>Key<\/strong>: Property name if it is present otherwise it will be index<br \/>\n<strong>value<\/strong>: Property value<br \/>\n<strong>type<\/strong>: An integer number that represents the type of the value. Next example explains all the possible type values.<\/p>\n<p style=\"text-align: justify;\"><strong>Example 2:<\/strong> Example explaining all the possible type column values in case of an OPENJSON function with default schema<\/p>\n<pre class=\"brush: sql; gutter: false\">\r\nDECLARE @json_string NVARCHAR(MAX) = N&#039;\r\n\t{&quot;Name&quot;:&quot;Basavaraj&quot;,&quot;Id&quot;:1,&quot;Permanent&quot;:true,\r\n\t\t &quot;Hobbies&quot;:[&quot;Blogging&quot;,&quot;Cricket&quot;],\r\n\t\t&quot;Address&quot;:{&quot;State&quot;:&quot;KA&quot;,&quot;Country&quot;:&quot;India&quot;}}&#039;\r\nSELECT *\r\nFROM OPENJSON(@json_string)\r\n<\/pre>\n<p><strong>RESULT:<\/strong><br \/>\n<a href=\"https:\/\/sqlhints.com\/wp-content\/uploads\/2015\/11\/OPENJSON-with-out-schema-declaration-Example-2.jpg\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/sqlhints.com\/wp-content\/uploads\/2015\/11\/OPENJSON-with-out-schema-declaration-Example-2.jpg\" alt=\"OPENJSON with out schema declaration Example 2\" width=\"500\" height=\"370\" class=\"alignnone size-full wp-image-4163\" \/><\/a><\/p>\n<p style=\"text-align: justify;\">So from the result we can say that type clumn value will be 1 in case the value is STRING, 2 if the value is of type INT, 3 if the value is of type Boolean, 4 if value is of the type JSON array and 5 if the value is of the type JSON object.<\/p>\n<p style=\"text-align: justify;\"><strong>Example 3:<\/strong> Fetching only the key and value columns (i.e. in this example not fetching the type column)<\/p>\n<pre class=\"brush: sql; gutter: false\">\r\nDECLARE @json_string NVARCHAR(MAX) = N&#039;\r\n\t{&quot;Id&quot;:1,&quot;Name&quot;:&quot;Basavaraj&quot;,\r\n\t\t&quot;Address&quot;:{&quot;State&quot;:&quot;KA&quot;,&quot;Country&quot;:&quot;India&quot;}}&#039;\r\nSELECT [key],value\r\nFROM OPENJSON(@json_string)\r\n<\/pre>\n<p><strong>RESULT:<\/strong><br \/>\n<a href=\"https:\/\/sqlhints.com\/wp-content\/uploads\/2015\/11\/OPENJSON-with-default-schema-Example-3.jpg\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/sqlhints.com\/wp-content\/uploads\/2015\/11\/OPENJSON-with-default-schema-Example-3.jpg\" alt=\"OPENJSON with default schema Example 3\" width=\"500\" height=\"290\" class=\"alignnone size-full wp-image-4164\" \/><\/a><\/p>\n<p style=\"text-align: justify;\"><strong>Example 4:<\/strong> In this example specifying the json_path in the OPENJSON function which is pointing to Address JSON object in the input JSON string<\/p>\n<pre class=\"brush: sql; gutter: false\">\r\nDECLARE @json_string NVARCHAR(MAX) = N&#039;\r\n  {&quot;Id&quot;:1,&quot;Name&quot;:&quot;Basavaraj&quot;,\r\n   &quot;Address&quot;:{&quot;State&quot;:&quot;KA&quot;,&quot;Country&quot;:&quot;India&quot;}}&#039;\r\nSELECT [key],value\r\nFROM OPENJSON(@json_string, &#039;$.Address&#039;)\r\n<\/pre>\n<p><strong>RESULT:<\/strong><br \/>\n<a href=\"https:\/\/sqlhints.com\/wp-content\/uploads\/2015\/11\/OPENJSON-with-default-schema-Example-4.jpg\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/sqlhints.com\/wp-content\/uploads\/2015\/11\/OPENJSON-with-default-schema-Example-4.jpg\" alt=\"OPENJSON with default schema  Example 4\" width=\"500\" height=\"261\" class=\"alignnone size-full wp-image-4165\" \/><\/a><\/p>\n<p style=\"text-align: justify;\"><strong>Example 5:<\/strong> One more example where the JSON text has only one attribute at the root level and this attribute is pointing to a nested JSON string<\/p>\n<pre class=\"brush: sql; gutter: false\">\r\nDECLARE @json_string NVARCHAR(MAX) = N&#039;\r\n  {&quot;Customer&quot;:{&quot;Id&quot;:1,&quot;Name&quot;:&quot;Basavaraj&quot;,\r\n   &quot;Address&quot;:{&quot;State&quot;:&quot;KA&quot;,&quot;Country&quot;:&quot;India&quot;}}}&#039;\r\nSELECT [key],value\r\nFROM OPENJSON(@json_string)<\/pre>\n<p><strong>RESULT:<\/strong><br \/>\n<a href=\"https:\/\/sqlhints.com\/wp-content\/uploads\/2015\/11\/OPENJSON-function-with-default-schema-Example-5.jpg\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/sqlhints.com\/wp-content\/uploads\/2015\/11\/OPENJSON-function-with-default-schema-Example-5.jpg\" alt=\"OPENJSON function with default schema Example 5\" width=\"500\" height=\"241\" class=\"alignnone size-full wp-image-4166\" \/><\/a><\/p>\n<p style=\"text-align: justify;\"><strong>Example 6:<\/strong> Input JSON string is a JSON array of strings.<\/p>\n<pre class=\"brush: sql; gutter: false\">\r\nDECLARE @json_string NVARCHAR(MAX) = &#039;[&quot;Blogging&quot;,&quot;Cricket&quot;]&#039;\r\nSELECT [key],value\r\nFROM OPENJSON(@json_string)\r\n<\/pre>\n<p><strong>RESULT:<\/strong><br \/>\n<a href=\"https:\/\/sqlhints.com\/wp-content\/uploads\/2015\/11\/OPENJSON-function-with-default-schema-Example-6.jpg\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/sqlhints.com\/wp-content\/uploads\/2015\/11\/OPENJSON-function-with-default-schema-Example-6.jpg\" alt=\"OPENJSON function with default schema Example 6\" width=\"420\" height=\"238\" class=\"alignnone size-full wp-image-4167\" srcset=\"https:\/\/sqlhints.com\/wp-content\/uploads\/2015\/11\/OPENJSON-function-with-default-schema-Example-6.jpg 420w, https:\/\/sqlhints.com\/wp-content\/uploads\/2015\/11\/OPENJSON-function-with-default-schema-Example-6-300x170.jpg 300w\" sizes=\"auto, (max-width: 420px) 100vw, 420px\" \/><\/a><\/p>\n<p style=\"text-align: justify;\"><strong>Example 7:<\/strong> Input JSON string is a JSON array of integers.<\/p>\n<pre class=\"brush: sql; gutter: false\">\r\nDECLARE @json_string NVARCHAR(MAX) = &#039;[1,2,3,4]&#039;\r\nSELECT [key],value\r\nFROM OPENJSON(@json_string)<\/pre>\n<p><strong>RESULT:<\/strong><br \/>\n<a href=\"https:\/\/sqlhints.com\/wp-content\/uploads\/2015\/11\/OPENJSON-function-with-default-schema-Example-7.jpg\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/sqlhints.com\/wp-content\/uploads\/2015\/11\/OPENJSON-function-with-default-schema-Example-7.jpg\" alt=\"OPENJSON function with default schema Example 7\" width=\"500\" height=\"267\" class=\"alignnone size-full wp-image-4168\" \/><\/a><\/p>\n<p style=\"text-align: justify;\">From the results of example 6 and 7, we can see that OPENJSON function converts the JSON array to a result set. So such feature will be useful in converting an array of input values to a temporary result and joining with other tables.<\/p>\n<p style=\"text-align: justify;\"><strong>Example 8:<\/strong> In this example specifying the json_path in the OPENJSON function which is pointing to Hobbies JSON array in the input JSON string<\/p>\n<pre class=\"brush: sql; gutter: false\">\r\nDECLARE @json_string NVARCHAR(MAX) = N&#039;\r\n    {&quot;Id&quot;:1,&quot;Name&quot;:&quot;Basavaraj&quot;,\r\n        &quot;Hobbies&quot;:[&quot;Blogging&quot;,&quot;Cricket&quot;]}&#039;\r\nSELECT [key],value\r\nFROM OPENJSON(@json_string,&#039;$.Hobbies&#039;)\r\n<\/pre>\n<p><strong>RESULT:<\/strong><br \/>\n<a href=\"https:\/\/sqlhints.com\/wp-content\/uploads\/2015\/11\/OPENJSON-function-with-default-schema-Example-8.jpg\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/sqlhints.com\/wp-content\/uploads\/2015\/11\/OPENJSON-function-with-default-schema-Example-8.jpg\" alt=\"OPENJSON function with default schema Example 8\" width=\"500\" height=\"235\" class=\"alignnone size-full wp-image-4169\" \/><\/a><\/p>\n<p style=\"text-align: justify;\"><strong>Example 9:<\/strong> In this example the input JSON string is an array of nested JSON objects<\/p>\n<pre class=\"brush: sql; gutter: false\">\r\nDECLARE @json_string NVARCHAR(MAX) = \r\n N&#039;{&quot;Customers&quot;:\r\n    [{&quot;Id&quot;:1,&quot;Name&quot;:&quot;Basavaraj&quot;,\r\n            &quot;Address&quot;:{&quot;State&quot;:&quot;KA&quot;,&quot;Country&quot;:&quot;India&quot;}},\r\n     {&quot;Id&quot;:2,&quot;Name&quot;:&quot;Kalpana&quot;,\r\n            &quot;Address&quot;:{&quot;State&quot;:&quot;NY&quot;,&quot;Country&quot;:&quot;United State&quot;}}\r\n    ]\r\n }&#039;\r\nSELECT *\r\nFROM OPENJSON(@json_string)\r\n<\/pre>\n<p><strong>RESULT:<\/strong><br \/>\n<a href=\"https:\/\/sqlhints.com\/wp-content\/uploads\/2015\/11\/OPENJSON-function-with-default-schema-Example-9-1.jpg\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/sqlhints.com\/wp-content\/uploads\/2015\/11\/OPENJSON-function-with-default-schema-Example-9-1.jpg\" alt=\"OPENJSON function with default schema Example 9 1\" width=\"500\" height=\"367\" class=\"alignnone size-full wp-image-4170\" \/><\/a><\/p>\n<p style=\"text-align: justify;\">Let us re-execute the above example by specifying the json_path which is pointing to an array of JSON objects<\/p>\n<pre class=\"brush: sql; gutter: false\">\r\nDECLARE @json_string NVARCHAR(MAX) = \r\n N&#039;{&quot;Customers&quot;:\r\n    [{&quot;Id&quot;:1,&quot;Name&quot;:&quot;Basavaraj&quot;,\r\n            &quot;Address&quot;:{&quot;State&quot;:&quot;KA&quot;,&quot;Country&quot;:&quot;India&quot;}},\r\n     {&quot;Id&quot;:2,&quot;Name&quot;:&quot;Kalpana&quot;,\r\n            &quot;Address&quot;:{&quot;State&quot;:&quot;NY&quot;,&quot;Country&quot;:&quot;United State&quot;}}\r\n    ]\r\n }&#039;\r\nSELECT *\r\nFROM OPENJSON(@json_string,&#039;$.Customers&#039;)\r\n<\/pre>\n<p><strong>RESULT:<\/strong><br \/>\n<a href=\"https:\/\/sqlhints.com\/wp-content\/uploads\/2015\/11\/OPENJSON-function-with-default-schema-Example-9-2.jpg\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/sqlhints.com\/wp-content\/uploads\/2015\/11\/OPENJSON-function-with-default-schema-Example-9-2.jpg\" alt=\"OPENJSON function with default schema Example 9 2\" width=\"500\" height=\"385\" class=\"alignnone size-full wp-image-4171\" \/><\/a><\/p>\n<h3>OPENJSON examples with explicit schema definition<\/h3>\n<p style=\"text-align: justify;\"><strong>Example 1<\/strong>: This is an example of an OPENJSON function with explicit schema declaration.<\/p>\n<pre class=\"brush: sql; gutter: false\">\r\nDECLARE @json_string NVARCHAR(MAX) = \r\n N&#039;{&quot;Customers&quot;:\r\n    [{&quot;Id&quot;:1,&quot;Name&quot;:&quot;Basavaraj&quot;,\r\n      &quot;Address&quot;:{&quot;State&quot;:&quot;KA&quot;,&quot;Country&quot;:&quot;India&quot;}},\r\n     {&quot;Id&quot;:2,&quot;Name&quot;:&quot;Kalpana&quot;,\r\n      &quot;Address&quot;:{&quot;State&quot;:&quot;NY&quot;,&quot;Country&quot;:&quot;United State&quot;}}\r\n    ]\r\n }&#039;\r\nSELECT *\r\nFROM OPENJSON(@json_string,&#039;$.Customers&#039;)\r\nWITH(Id INT, Name NVarchar(100))<\/pre>\n<p><strong>RESULT:<\/strong><br \/>\n<a href=\"https:\/\/sqlhints.com\/wp-content\/uploads\/2015\/11\/OPENJSON-with-explicit-schema-declaration-Example-1.jpg\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/sqlhints.com\/wp-content\/uploads\/2015\/11\/OPENJSON-with-explicit-schema-declaration-Example-1.jpg\" alt=\"OPENJSON with explicit schema declaration Example 1\" width=\"500\" height=\"423\" class=\"alignnone size-full wp-image-4172\" \/><\/a><\/p>\n<p style=\"text-align: justify;\">In this example the json_path in the OPENJSON function points to the array of the JSON objects. In the schema declaration columns values json path is not mentioned, in such cases it tries to match the JSON property name by column name and returns its corresponding JSON property value.<\/p>\n<p style=\"text-align: justify;\"><strong>Example 2:<\/strong> Let us extended the example 1, in this example try to get the customer Country from the nested JSON object Address apart from Id and Name.<\/p>\n<pre class=\"brush: sql; gutter: false\">\r\nDECLARE @json_string NVARCHAR(MAX) = \r\n N&#039;{&quot;Customers&quot;:\r\n    [{&quot;Id&quot;:1,&quot;Name&quot;:&quot;Basavaraj&quot;,\r\n            &quot;Address&quot;:{&quot;State&quot;:&quot;KA&quot;,&quot;Country&quot;:&quot;India&quot;}},\r\n     {&quot;Id&quot;:2,&quot;Name&quot;:&quot;Kalpana&quot;,\r\n            &quot;Address&quot;:{&quot;State&quot;:&quot;NY&quot;,&quot;Country&quot;:&quot;United State&quot;}}\r\n    ]\r\n }&#039;\r\nSELECT *\r\nFROM OPENJSON(@json_string,&#039;$.Customers&#039;)\r\nWITH(Id INT, Name Varchar(100), [Address.Country] NVarchar(50))\r\n<\/pre>\n<p><strong>RESULT:<\/strong><br \/>\n<a href=\"https:\/\/sqlhints.com\/wp-content\/uploads\/2015\/11\/OPENJSON-with-explicit-schema-definition-Example-2.jpg\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/sqlhints.com\/wp-content\/uploads\/2015\/11\/OPENJSON-with-explicit-schema-definition-Example-2.jpg\" alt=\"OPENJSON with explicit schema definition Example 2\" width=\"500\" height=\"424\" class=\"alignnone size-full wp-image-4173\" \/><\/a><\/p>\n<p style=\"text-align: justify;\">Again in this example the column values json path is not mentioned, so it goes by the column name. So the column name [Address.Country] implies the OPEJSON function returns the Country property value from the Address JSON object.<\/p>\n<p style=\"text-align: justify;\"><strong>Example 3:<\/strong> In this example EmployeeId column\u2019s value json path is specified. Other columns (i.e. Name and Address.Country)  go by the column name in the input json text for it\u2019s value as explained in the previous example<\/p>\n<pre class=\"brush: sql; gutter: false\">\r\nDECLARE @json_string NVARCHAR(MAX) = \r\n N&#039;{&quot;Customers&quot;:\r\n    [{&quot;Id&quot;:1,&quot;Name&quot;:&quot;Basavaraj&quot;,\r\n      &quot;Address&quot;:{&quot;State&quot;:&quot;KA&quot;,&quot;Country&quot;:&quot;India&quot;}},\r\n     {&quot;Id&quot;:2,&quot;Name&quot;:&quot;Kalpana&quot;,\r\n      &quot;Address&quot;:{&quot;State&quot;:&quot;NY&quot;,&quot;Country&quot;:&quot;United State&quot;}}\r\n    ]\r\n }&#039;\r\nSELECT *\r\nFROM OPENJSON(@json_string,&#039;$.Customers&#039;)\r\nWITH(EmployeeId INT &#039;$.Id&#039;, Name Varchar(100), \r\n     [Address.Country] NVarchar(50))\r\n<\/pre>\n<p><strong>RESULT:<\/strong><br \/>\n<a href=\"https:\/\/sqlhints.com\/wp-content\/uploads\/2015\/11\/OPENJSON-with-explicit-schema-definition-Example-3.jpg\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/sqlhints.com\/wp-content\/uploads\/2015\/11\/OPENJSON-with-explicit-schema-definition-Example-3.jpg\" alt=\"OPENJSON with explicit schema definition Example 3\" width=\"500\" height=\"443\" class=\"alignnone size-full wp-image-4174\" \/><\/a><\/p>\n<p style=\"text-align: justify;\">If column value JSON path is mentioned then it overrides the column name approach to extract the column value. So from the result it is clear that, EmployeeId column value is extracted by the column value json path i.e. \u2018$.id\u2019, if it would have gone by the column name then we would have not got any value for this column because there is no property in the input JSON string with key name as EmployeeId.<\/p>\n<p style=\"text-align: justify;\"><strong>Example 4:<\/strong> In this example for all the columns the column value JSON path is defined<\/p>\n<pre class=\"brush: sql; gutter: false\"> \r\nDECLARE @json_string NVARCHAR(MAX) = \r\n N&#039;{&quot;Customers&quot;:\r\n    [{&quot;Id&quot;:1,&quot;Name&quot;:&quot;Basavaraj&quot;,\r\n            &quot;Address&quot;:{&quot;State&quot;:&quot;KA&quot;,&quot;Country&quot;:&quot;India&quot;}},\r\n     {&quot;Id&quot;:2,&quot;Name&quot;:&quot;Kalpana&quot;,\r\n            &quot;Address&quot;:{&quot;State&quot;:&quot;NY&quot;,&quot;Country&quot;:&quot;United State&quot;}}\r\n    ]\r\n }&#039;\r\nSELECT *\r\nFROM OPENJSON(@json_string,&#039;$.Customers&#039;)\r\nWITH(EmployeeId INT &#039;$.Id&#039;, EmployeeName Varchar(100) &#039;$.Name&#039;, \r\n\t\tCountry NVarchar(50) &#039;$.Address.Country&#039;)\r\n<\/pre>\n<p><strong>RESULT:<\/strong><br \/>\n<a href=\"https:\/\/sqlhints.com\/wp-content\/uploads\/2015\/11\/OPENJSON-with-explicit-schema-definition-Example-4.jpg\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/sqlhints.com\/wp-content\/uploads\/2015\/11\/OPENJSON-with-explicit-schema-definition-Example-4.jpg\" alt=\"OPENJSON with explicit schema definition Example 4\" width=\"500\" height=\"441\" class=\"alignnone size-full wp-image-4175\" \/><\/a><br \/>\n<strong>[ALSO READ]:<a href=\"https:\/\/sqlhints.com\/2015\/11\/15\/lax-and-strict-json-path-modes-in-sql-server-2016\/\" target=\"_blank\">lax and strict JSON Path modes in Sql Server 2016<\/a><\/strong><\/p>\n<h3>lax and strict JSON path modes influence on the OPENJSON JSON function output<\/h3>\n<p style=\"text-align: justify;\">let us understand the influence of the lax\/strict JSON path modes on the OPENJSON function output with extensive list of examples<\/p>\n<p style=\"text-align: justify;\"><strong>Example 1:<\/strong> In the below example the json_path &#8216;$.City&#8217; mentioned in the OPENJSON function doesn\u2019t exists in the input JSON string. And in this json_path the JSON path mode has not be specified explicitly, so it goes with default JSON path mode lax.<\/p>\n<pre class=\"brush: sql; gutter: false\">\r\nDECLARE @json_string NVARCHAR(MAX) = N&#039;\r\n{&quot;Id&quot;:1,&quot;Name&quot;:&quot;Basavaraj&quot;,\r\n  &quot;Address&quot;:{&quot;State&quot;:&quot;KA&quot;,&quot;Country&quot;:&quot;India&quot;}}&#039;\r\nSELECT [key],value\r\nFROM OPENJSON(@json_string, &#039;$.City&#039;)\r\n<\/pre>\n<p><strong>RESULT:<\/strong><br \/>\n<a href=\"https:\/\/sqlhints.com\/wp-content\/uploads\/2015\/11\/OPENJSON-lax-and-strict-JSON-path-modes-Example-1.jpg\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/sqlhints.com\/wp-content\/uploads\/2015\/11\/OPENJSON-lax-and-strict-JSON-path-modes-Example-1.jpg\" alt=\"OPENJSON lax and strict JSON path modes Example 1\" width=\"500\" height=\"259\" class=\"alignnone size-full wp-image-4176\" \/><\/a><\/p>\n<p style=\"text-align: justify;\">Let us execute this example by specifying the JSON path mode as <strong>strict<\/strong><\/p>\n<pre class=\"brush: sql; gutter: false\">\r\nDECLARE @json_string NVARCHAR(MAX) = \r\n\tN&#039;{&quot;Id&quot;:1,&quot;Name&quot;:&quot;Basavaraj&quot;,\r\n\t   &quot;Address&quot;:{&quot;State&quot;:&quot;KA&quot;,&quot;Country&quot;:&quot;India&quot;}}&#039;\r\nSELECT [key],value\r\nFROM OPENJSON(@json_string, &#039;strict$.City&#039;)\r\n<\/pre>\n<p><strong>RESULT:<\/strong><\/p>\n<p style=\"text-align: justify;color: red;\">Msg 13608, Level 16, State 3, Line 4<br \/>\nProperty cannot be found in specified path.<\/p>\n<p style=\"text-align: justify;\">Let us execute this example by explicitly specifying the <strong>default <\/strong>JSON path mode <strong>lax<\/strong><\/p>\n<pre class=\"brush: sql; gutter: false\">\r\nDECLARE @json_string NVARCHAR(MAX) = \r\n\tN&#039;{&quot;Id&quot;:1,&quot;Name&quot;:&quot;Basavaraj&quot;,\r\n\t   &quot;Address&quot;:{&quot;State&quot;:&quot;KA&quot;,&quot;Country&quot;:&quot;India&quot;}}&#039;\r\nSELECT [key],value\r\nFROM OPENJSON(@json_string, &#039;lax$.City&#039;)\r\n<\/pre>\n<p><strong>RESULT:<\/strong><br \/>\n<a href=\"https:\/\/sqlhints.com\/wp-content\/uploads\/2015\/11\/OPENJSON-lax-and-strict-JSON-path-modes-Example-1-2.jpg\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/sqlhints.com\/wp-content\/uploads\/2015\/11\/OPENJSON-lax-and-strict-JSON-path-modes-Example-1-2.jpg\" alt=\"OPENJSON lax and strict JSON path modes Example 1 2\" width=\"500\" height=\"246\" class=\"alignnone size-full wp-image-4177\" \/><\/a><\/p>\n<p style=\"text-align: justify;\"><strong>Example 2:<\/strong> In the below OPENJSON function with explicit schema declaration, DOB column value JSON path &#8216;$.Dob&#8217; doesn\u2019t exists in the input JSON string. In the json_path the JSON path mode has not be specified explicitly, so it goes with default JSON path mode which is lax.<\/p>\n<pre class=\"brush: sql; gutter: false\">\r\nDECLARE @json_string NVARCHAR(MAX) = \r\n N&#039;{&quot;Customers&quot;:\r\n    [{&quot;Id&quot;:1,&quot;Name&quot;:&quot;Basavaraj&quot;,\r\n            &quot;Address&quot;:{&quot;State&quot;:&quot;KA&quot;,&quot;Country&quot;:&quot;India&quot;}},\r\n     {&quot;Id&quot;:2,&quot;Name&quot;:&quot;Kalpana&quot;,\r\n            &quot;Address&quot;:{&quot;State&quot;:&quot;NY&quot;,&quot;Country&quot;:&quot;United State&quot;}}\r\n    ]\r\n }&#039;\r\nSELECT *\r\nFROM OPENJSON(@json_string,&#039;$.Customers&#039;)\r\nWITH(EmployeeId INT &#039;$.Id&#039;, EmployeeName Varchar(100) &#039;$.Name&#039;,\r\n\t\tDOB Varchar(10) &#039;$.Dob&#039;)\r\n<\/pre>\n<p><strong>RESULT:<\/strong><br \/>\n<a href=\"https:\/\/sqlhints.com\/wp-content\/uploads\/2015\/11\/OPENJSON-lax-and-strict-JSON-path-modes-Example-2.jpg\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/sqlhints.com\/wp-content\/uploads\/2015\/11\/OPENJSON-lax-and-strict-JSON-path-modes-Example-2.jpg\" alt=\"OPENJSON lax and strict JSON path modes Example 2\" width=\"500\" height=\"450\" class=\"alignnone size-full wp-image-4178\" \/><\/a><\/p>\n<p style=\"text-align: justify;\">Let us execute the previous query by specifying the JSON path mode as strict i.e. &#8216;strict$.Dob&#8217;<\/p>\n<pre class=\"brush: sql; gutter: false\">\r\nDECLARE @json_string NVARCHAR(MAX) = N&#039;\r\n{&quot;Customers&quot;:\r\n    [{&quot;Id&quot;:1,&quot;Name&quot;:&quot;Basavaraj&quot;,\r\n      &quot;Address&quot;:{&quot;State&quot;:&quot;KA&quot;,&quot;Country&quot;:&quot;India&quot;}},\r\n     {&quot;Id&quot;:2,&quot;Name&quot;:&quot;Kalpana&quot;,\r\n      &quot;Address&quot;:{&quot;State&quot;:&quot;NY&quot;,&quot;Country&quot;:&quot;United State&quot;}}\r\n    ]\r\n }&#039;\r\nSELECT *\r\nFROM OPENJSON(@json_string,&#039;$.Customers&#039;)\r\nWITH(EmployeeId INT &#039;$.Id&#039;, EmployeeName Varchar(100) &#039;$.Name&#039;,\r\n\t\tDOB Varchar(10) &#039;strict$.Dob&#039;)\r\n<\/pre>\n<p><strong>RESULT:<\/strong><\/p>\n<p style=\"text-align: justify;color: red;\">\nMsg 13608, Level 16, State 6, Line 9<br \/>\nProperty cannot be found in specified path.\n<\/p>\n<p style=\"text-align: justify;\">Let us execute the above query by specifying the default JSON path mode explicitly &#8216;lax$.Dob&#8217;<\/p>\n<pre class=\"brush: sql; gutter: false\">\r\nDECLARE @json_string NVARCHAR(MAX) = N&#039;\r\n{&quot;Customers&quot;:\r\n    [{&quot;Id&quot;:1,&quot;Name&quot;:&quot;Basavaraj&quot;,\r\n      &quot;Address&quot;:{&quot;State&quot;:&quot;KA&quot;,&quot;Country&quot;:&quot;India&quot;}},\r\n     {&quot;Id&quot;:2,&quot;Name&quot;:&quot;Kalpana&quot;,\r\n      &quot;Address&quot;:{&quot;State&quot;:&quot;NY&quot;,&quot;Country&quot;:&quot;United State&quot;}}\r\n    ]\r\n }&#039;\r\nSELECT *\r\nFROM OPENJSON(@json_string,&#039;$.Customers&#039;)\r\nWITH(EmployeeId INT &#039;$.Id&#039;, EmployeeName Varchar(100) &#039;$.Name&#039;,\r\n\t\tDOB Varchar(10) &#039;lax$.Dob&#039;)\r\n<\/pre>\n<p><strong>RESULT:<\/strong><br \/>\n <a href=\"https:\/\/sqlhints.com\/wp-content\/uploads\/2015\/11\/OPENJSON-lax-and-strict-JSON-path-modes-Example-2-2.jpg\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/sqlhints.com\/wp-content\/uploads\/2015\/11\/OPENJSON-lax-and-strict-JSON-path-modes-Example-2-2.jpg\" alt=\"OPENJSON lax and strict JSON path modes Example 2 2\" width=\"500\" height=\"449\" class=\"alignnone size-full wp-image-4179\" \/><\/a><br \/>\n<strong>[ALSO READ]:<\/strong><\/p>\n<ul>\n<li><a href=\"https:\/\/sqlhints.com\/2015\/10\/25\/json-in-sql-server-2016\/\" target=\"_blank\">Native JSON Support in Sql Server 2016<\/a>\n<ul>\n<li><a href=\"https:\/\/sqlhints.com\/2015\/11\/15\/for-json-clause-in-sql-server-2016\/\" target=\"_blank\">FOR JSON Clause in Sql Server 2016<\/a><\/li>\n<li><a href=\"https:\/\/sqlhints.com\/2015\/11\/22\/openjson-function-in-sql-server-2016\/\" target=\"_blank\">OPENJSON Function in Sql Server 2016<\/a><\/li>\n<li><a href=\"https:\/\/sqlhints.com\/2015\/11\/15\/isjson-function-in-sql-server-2016\/\" target=\"_blank\">ISJSON Function in Sql Server 2016<\/a><\/li>\n<li><a href=\"https:\/\/sqlhints.com\/2015\/11\/15\/json_value-function-in-sql-server-2016\/\" target=\"_blank\">JSON_VALUE Function in Sql Server 2016<\/a><\/li>\n<li><a href=\"https:\/\/sqlhints.com\/2015\/11\/15\/json_query-function-in-sql-server-2016\/\" target=\"_blank\">JSON_QUERY Function in Sql Server 2016<\/a><\/li>\n<li><a href=\"https:\/\/sqlhints.com\/2015\/11\/15\/lax-and-strict-json-path-modes-in-sql-server-2016\/\" target=\"_blank\">lax and strict JSON Path modes in Sql Server 2016<\/a><\/li>\n<li><a href=\"https:\/\/sqlhints.com\/2015\/11\/28\/indexing-strategy-for-json-value-in-sql-server-2016\/\" target=\"_blank\">Indexing Strategy for JSON Value in Sql Server 2016<\/a><\/li>\n<\/ul>\n<\/li>\n<li><a href=\"https:\/\/sqlhints.com\/2015\/11\/12\/drop-if-exists-statement-in-sql-server-2016\/\" target=\"_blank\">DROP IF EXISTS Statement in Sql Server 2016<\/a><\/li>\n<li><a href=\"https:\/\/sqlhints.com\/2015\/11\/29\/compare-execution-plan-in-sql-server-2016\/\" target=\"_blank\">Compare Execution Plans in Sql Server 2016<\/a><\/li>\n<li><a href=\"https:\/\/sqlhints.com\/2015\/12\/11\/live-query-statistics-in-sql-server-2016\/\" target=\"_blank\">Live Query Statistics in Sql Server 2016<\/a><\/li>\n<li><a href=\"https:\/\/sqlhints.com\/2015\/12\/12\/datediff_big-function-in-sql-server-2016\/\" target=\"_blank\">DATEDIFF_BIG Function in Sql Server 2016<\/a><\/li>\n<li><a href=\"https:\/\/sqlhints.com\/2015\/12\/12\/difference-between-datediff-and-datediff_big-functions-in-sql-server\/\" target=\"_blank\">Difference between DATEDIFF and DATEDIFF_BIG functions in Sql Server<\/a><\/li>\n<li><a href=\"https:\/\/sqlhints.com\/2015\/12\/15\/session_context-in-sql-server-2016\/\" target=\"_blank\">SESSION_CONTEXT in Sql Server 2016<\/a><\/li>\n<\/ul>\n","protected":false},"excerpt":{"rendered":"<p>OPENJSON is one of the new JSON function introduced in Sql Server 2016, it is very much similar to the OPENXML function. This table valued function can be used to transform the JSON text to one or many rows. Basically, it provides a row-set view of the input JSON string. Syntax: OPENJSON( json_string [, json_path &hellip; <a href=\"https:\/\/sqlhints.com\/2015\/11\/22\/openjson-function-in-sql-server-2016\/\" class=\"more-link\">Continue reading <span class=\"screen-reader-text\">OPENJSON Function in Sql Server 2016<\/span> <span class=\"meta-nav\">&rarr;<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":true,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2}},"categories":[3,830],"tags":[913,909,914,915,871,832,895,902,903,901,896,849,897,898,900,899,321,986,834],"class_list":["post-4160","post","type-post","status-publish","format-standard","hentry","category-sql-server","category-sql-server-2016","tag-json-functions","tag-json-functions-in-sql","tag-json-functions-in-sql-server","tag-json-functions-in-sql-server-2016","tag-json-in-sql","tag-json-in-sql-server","tag-json-in-sql-server-2016","tag-json-rowset-view","tag-json-text-to-result-set","tag-json-to-rows-in-sql","tag-openjson","tag-openjson-sql","tag-openjson-sql-function","tag-openjson-sql-server","tag-openjson-with-schema-declaration","tag-openjson-with-schema-definition","tag-sql","tag-sql-server","tag-sql-server-2016"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p3xNAz-156","_links":{"self":[{"href":"https:\/\/sqlhints.com\/wp-json\/wp\/v2\/posts\/4160","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/sqlhints.com\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/sqlhints.com\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/sqlhints.com\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/sqlhints.com\/wp-json\/wp\/v2\/comments?post=4160"}],"version-history":[{"count":14,"href":"https:\/\/sqlhints.com\/wp-json\/wp\/v2\/posts\/4160\/revisions"}],"predecessor-version":[{"id":4377,"href":"https:\/\/sqlhints.com\/wp-json\/wp\/v2\/posts\/4160\/revisions\/4377"}],"wp:attachment":[{"href":"https:\/\/sqlhints.com\/wp-json\/wp\/v2\/media?parent=4160"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/sqlhints.com\/wp-json\/wp\/v2\/categories?post=4160"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/sqlhints.com\/wp-json\/wp\/v2\/tags?post=4160"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}