{"id":656,"date":"2019-10-01T20:09:32","date_gmt":"2019-10-01T14:39:32","guid":{"rendered":"https:\/\/techieshouts.com\/?p=656"},"modified":"2022-08-09T19:05:53","modified_gmt":"2022-08-09T13:35:53","slug":"json-built-in-functions-in-sql-server","status":"publish","type":"post","link":"https:\/\/techieshouts.com\/home\/json-built-in-functions-in-sql-server\/","title":{"rendered":"JSON Built in functions in SQL Server"},"content":{"rendered":"\n<p>In this post, I will explain JSON built in functions in SQL server to work with JSON string. The JSON built in functions introduced in SQL Server 2016 are,<\/p>\n\n\n\n<ul><li>OPENJSON<\/li><li>ISJSON<\/li><li>JSON_VALUE<\/li><li>JSON_QUERY<\/li><li>JSON_MODIFY<\/li><\/ul>\n\n\n\n<p>In the previous post, I explained <a href=\"https:\/\/techieshouts.com\/openjson-function-in-sql-server\/\">OPENJSON<\/a> function in detail with examples. Now, I will explain rest of the functions with examples.<\/p>\n\n\n\n<h2>ISJSON<\/h2>\n\n\n\n<p>ISJSON\nfunction validates the JSON string is correctly formatted or not. It returns 1\nwhen the JSON is valid and 0 when invalid.<\/p>\n\n\n\n<p><em>ISJSON(JSON_String)<\/em><\/p>\n\n\n\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"sql\" data-enlighter-theme=\"\" data-enlighter-highlight=\"\" data-enlighter-linenumbers=\"\" data-enlighter-lineoffset=\"\" data-enlighter-title=\"\" data-enlighter-group=\"\">--Valid JSON returns 1\nSELECT ISJSON(' {\"Name\":\"Sean Paul\", \"Gender\":\"M\"}')\nSELECT ISJSON(' {\"Name\":\"Sean Paul\", \"Age\":30}')\n\n--Invalid JSON returns 0\nSELECT ISJSON(' {\"Name\":\"Sean Paul\", \"Gender\":M}')<\/pre>\n\n\n\n<h2><strong>JSON_VALUE <\/strong><\/h2>\n\n\n\n<p>JSON_VALUE\nis the one of the built in functions to parse and extract a scalar value for a\nspecified key from the JSON string.<\/p>\n\n\n\n<p><em>JSON_VALUE(JSON_String, Path)<\/em><\/p>\n\n\n\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"sql\" data-enlighter-theme=\"\" data-enlighter-highlight=\"\" data-enlighter-linenumbers=\"\" data-enlighter-lineoffset=\"\" data-enlighter-title=\"\" data-enlighter-group=\"\">DECLARE @JSON NVARCHAR(1000) = N'{\"Name\":\"Sean Paul\", \"Gender\":\"M\", \"Skills\":[\"C#\", \"SQL\", \".NET\"], \"Address\":{\"State\":\"MS\",\"Country\":\"USA\",\"ZIP\":39759}}'\n--Returns the value of the property 'Name'\nSELECT JSON_VALUE(@JSON, '$.Name')\n--Returns the first value from the 'Skills' array\nSELECT JSON_VALUE(@JSON, '$.Skills[0]')\n--Returns the ZIP value from the 'Address' object\nSELECT JSON_VALUE(@JSON, '$.Address.ZIP')<\/pre>\n\n\n\n<h3><strong>Lax and Strict Mode<\/strong><\/h3>\n\n\n\n<p>It helps to determine whether the function should return NULL or an error message.  If the path specified is invalid or the return value is greater than 4000 characters: <\/p>\n\n\n\n<ul><li>In lax mode, JSON_VALUE function returns NULL.<\/li><li>In strict mode, JSON_VALUE function returns an error.<\/li><\/ul>\n\n\n\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"sql\" data-enlighter-theme=\"\" data-enlighter-highlight=\"\" data-enlighter-linenumbers=\"\" data-enlighter-lineoffset=\"\" data-enlighter-title=\"\" data-enlighter-group=\"\">DECLARE @JSON NVARCHAR(1000) = N'{\"Name\":\"Sean Paul\", \"Gender\":\"M\", \"Skills\":[\"C#\", \"SQL\", \".NET\"], \"Address\":{\"State\":\"MS\",\"Country\":\"USA\",\"ZIP\":39759}}'\n--Lax mode: The function returns NULL if the path is invalid\nSELECT JSON_VALUE(@JSON, 'lax $.Age')\n--Since it is the default mode, 'lax' keyword is not required\nSELECT JSON_VALUE(@JSON, '$.Age')\n--Strict mode: The function returns an error if the path is invalid\nSELECT JSON_VALUE(@JSON, 'strict $.Age')<\/pre>\n\n\n\n<p>The\nfinal query in the above example returns the error &#8216;Property cannot be found on\nthe specified JSON path&#8217;.<\/p>\n\n\n\n<h2><strong>JSON_QUERY<\/strong><\/h2>\n\n\n\n<p>JSON_QUERY\nfunction helps to extract the JSON array, object or array of objects from the\nJSON string.<\/p>\n\n\n\n<p><em>JSON_QUERY(JSON_String, Path)<\/em><\/p>\n\n\n\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"sql\" data-enlighter-theme=\"\" data-enlighter-highlight=\"\" data-enlighter-linenumbers=\"\" data-enlighter-lineoffset=\"\" data-enlighter-title=\"\" data-enlighter-group=\"\">DECLARE @JSON NVARCHAR(1000) = N'{\"Name\":\"Sean Paul\", \"Gender\":\"M\", \"Skills\":[\"C#\", \"SQL\", \".NET\"], \"Address\":{\"State\":\"MS\",\"Country\":\"USA\",\"ZIP\":39759}}'\n\n--Returns the 'Skills' array\nSELECT JSON_QUERY(@JSON, '$.Skills')\n--Returns the 'Address' object\nSELECT JSON_QUERY(@JSON, '$.Address')\n<\/pre>\n\n\n\n<h3><strong>Lax and Strict Mode<\/strong><\/h3>\n\n\n\n<p>If\nthe path specified is invalid or the return value is not an array or object:<\/p>\n\n\n\n<ul><li>In lax mode, JSON_QUERY function returns NULL.<\/li><li>In strict mode, JSON_QUERY function returns an error.<\/li><\/ul>\n\n\n\n<h3><strong>Example 1: <\/strong><\/h3>\n\n\n\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"sql\" data-enlighter-theme=\"\" data-enlighter-highlight=\"\" data-enlighter-linenumbers=\"\" data-enlighter-lineoffset=\"\" data-enlighter-title=\"\" data-enlighter-group=\"\">DECLARE @JSON NVARCHAR(1000) = N'{\"Name\":\"Sean Paul\", \"Gender\":\"M\", \"Skills\":[\"C#\", \"SQL\", \".NET\"], \"Address\":{\"State\":\"MS\",\"Country\":\"USA\",\"ZIP\":39759}}'\n\n--The type of 'Gender' property is not an array or object\n--Lax mode: Returns NULL \nSELECT JSON_QUERY(@JSON, 'lax $.Gender')\n--Strict mode: Returns Error \nSELECT JSON_QUERY(@JSON, 'strict $.Gender')<\/pre>\n\n\n\n<h3><strong>Example 2: <\/strong><\/h3>\n\n\n\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"sql\" data-enlighter-theme=\"\" data-enlighter-highlight=\"\" data-enlighter-linenumbers=\"\" data-enlighter-lineoffset=\"\" data-enlighter-title=\"\" data-enlighter-group=\"\">DECLARE @JSON NVARCHAR(1000) = N'{\"Name\":\"Sean Paul\", \"Gender\":\"M\", \"Skills\":[\"C#\", \"SQL\", \".NET\"], \"Address\":{\"State\":\"MS\",\"Country\":\"USA\",\"ZIP\":39759}}'\n\n--'Age' property is missing in the JSON string\n--Lax mode: Returns NULL\nSELECT JSON_QUERY(@JSON, 'lax $.Age')\n--Strict mode: Returns error\nSELECT JSON_QUERY(@JSON, 'strict $.Age')<\/pre>\n\n\n\n<p><\/p>\n","protected":false},"excerpt":{"rendered":"<p>In this post, I will explain JSON built in functions in SQL server to work with JSON string. The JSON built in functions introduced in SQL Server 2016 are, OPENJSON ISJSON JSON_VALUE JSON_QUERY JSON_MODIFY In the previous post, I explained OPENJSON function in detail with examples. Now, I will explain rest of the functions with\u2026 <span class=\"read-more\"><a href=\"https:\/\/techieshouts.com\/home\/json-built-in-functions-in-sql-server\/\">Read More &raquo;<\/a><\/span><\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":[],"categories":[10],"tags":[124,39,63],"_links":{"self":[{"href":"https:\/\/techieshouts.com\/home\/wp-json\/wp\/v2\/posts\/656"}],"collection":[{"href":"https:\/\/techieshouts.com\/home\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/techieshouts.com\/home\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/techieshouts.com\/home\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/techieshouts.com\/home\/wp-json\/wp\/v2\/comments?post=656"}],"version-history":[{"count":2,"href":"https:\/\/techieshouts.com\/home\/wp-json\/wp\/v2\/posts\/656\/revisions"}],"predecessor-version":[{"id":923,"href":"https:\/\/techieshouts.com\/home\/wp-json\/wp\/v2\/posts\/656\/revisions\/923"}],"wp:attachment":[{"href":"https:\/\/techieshouts.com\/home\/wp-json\/wp\/v2\/media?parent=656"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/techieshouts.com\/home\/wp-json\/wp\/v2\/categories?post=656"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/techieshouts.com\/home\/wp-json\/wp\/v2\/tags?post=656"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}