{"id":25997,"date":"2020-10-25T20:18:00","date_gmt":"2020-10-25T20:18:00","guid":{"rendered":"https:\/\/ittutorial.org\/?p=25997"},"modified":"2020-10-25T20:18:00","modified_gmt":"2020-10-25T20:18:00","slug":"json-in-sql-server","status":"publish","type":"post","link":"https:\/\/ittutorial.org\/json-in-sql-server\/","title":{"rendered":"JSON in SQL Server"},"content":{"rendered":"<div class=\"post-body text\">\n<p>JSON (<strong>J<\/strong>ava<strong>S<\/strong>cript <strong>O<\/strong>bject <strong>N<\/strong>otation) is very popular standard for exchanging data specially in REST API. Almost all modern websites, android or iOS app use this for exchanging data with server. From SQL Server 2016, Microsoft extends its support for JSON through couple of built-in functions. This way SQL Server supports NOSQL features in traditional relational databases. Let&#8217;s examine it:<\/p>\n<ul>\n<li>ISJSON &#8211; examines a string whether a valid JSON or not<\/li>\n<li>JSON_VALUE &#8211; retrieves value from a JSON string<\/li>\n<li>JSON_QUERY &#8211; fetches object or array from a JSON string<\/li>\n<li>JSON_MODIFY &#8211; returns the updated JSON string<\/li>\n<li>OPENJSON &#8211; parses JSON text and returns objects and properties from the JSON input as rows and columns<\/li>\n<li>FOR JSON Clause &#8211; exports SQL data to JSON format<\/li>\n<\/ul>\n<h4>ISJSON Function<\/h4>\n<pre>DECLARE @json NVARCHAR(MAX); \r\nSET @json = N'{\r\n\"info\":[\r\n{\r\n\"id\":\"1\",\r\n\"name\":\"Robert Aragon\",\r\n\"ssn\":\"489-36-8350\",\r\n\"credit_card\":[\r\n\"4929-3813-3266-4295\",\r\n\"5370-4638-8881-3020\"\r\n],\r\n\"address\":{\r\n\"town\":\"Avon\",\r\n\"area\":\"New York\",\r\n\"zipcode\":\"76148\"\r\n}\r\n},\r\n{\r\n\"id\":\"2\",\r\n\"name\":\"Thomas Conley\",\r\n\"ssn\":\"690-05-5315\",\r\n\"credit_card\":[\r\n\"5299-1561-5689-1938\"\r\n],\r\n\"address\":{\r\n\"town\":\"Jackson Street\",\r\n\"area\":\"New York\",\r\n\"zipcode\":\"80233\"\r\n}\r\n},\r\n{\r\n\"id\":\"3\",\r\n\"name\":\"Susan Davis\",\r\n\"ssn\":\"421-37-1396\",\r\n\"credit_card\":[\r\n\"5293-8502-0071-3058\"\r\n],\r\n\"address\":{\r\n\"town\":\"Rock Beach\",\r\n\"area\":\"Los angeles\",\r\n\"zipcode\":\"900341\"\r\n}\r\n},\r\n{\r\n\"id\":\"4\",\r\n\"name\":\"Christopher Diaz\",\r\n\"ssn\":\"458-02-6124\",\r\n\"credit_card\":[\r\n\"5548-0246-6336-5664\"\r\n],\r\n\"address\":{\r\n\"town\":\"Small town\",\r\n\"area\":\"Wasshington\",\r\n\"zipcode\":\"63126\"\r\n}\r\n},\r\n{\r\n\"id\":\"5\",\r\n\"name\":\"Rick Edwards\",\r\n\"ssn\":\"612-20-6832\",\r\n\"credit_card\":[\r\n\"4539-5385-7425-5825\"\r\n],\r\n\"address\":{\r\n\"town\":\"Free Town\",\r\n\"area\":\"Utah\",\r\n\"zipcode\":\"97222\"\r\n}\r\n},\r\n{\r\n\"id\":\"6\",\r\n\"name\":\"Victor Faulkner\",\r\n\"ssn\":\"300-62-3266\",\r\n\"credit_card\":[\r\n\"4916-9766-5240-6147\",\r\n\"4532-4220-6922-9909\",\r\n\"5218-0144-2703-9266\"\r\n],\r\n\"address\":{\r\n\"town\":\"Dakota\",\r\n\"area\":\"North Dakota\",\r\n\"zipcode\":\"92104\"\r\n}\r\n}\r\n]\r\n}\r\n'; \r\nSELECT ISJSON(@json);<\/pre>\n<p><strong>Output<\/strong><\/p>\n<pre>1 -- 1 if it is a valid JSON otherwise 0<\/pre>\n<h4>JSON_VALUE Function<\/h4>\n<p>This function is used to retrieve a scalar value from a JSON string. It&#8217;s syntax is<\/p>\n<pre>JSON_VALUE(expression, path)<\/pre>\n<p><em>expression<\/em> is the name of a variable or a column that contains JSON text and is the property to extract. For example, running the below statement on the above JSON string will yield below output :<\/p>\n<pre>SELECT JSON_VALUE(@json, '$.info[3].credit_card[0]')<\/pre>\n<p><strong>Output<\/strong><\/p>\n<pre>5548-0246-6336-5664<\/pre>\n<p>The JSON_VALUE function returns a single text value of type nvarchar(4000) . It returns <em>null <\/em>if specified path is not found in the JSON object or value is beyond nvarchar(4000).\u00a0 Using\u00a0&#8216;<em>strict<\/em>&#8216; keyword before the path will throw the error if specified path is not available in JSON object.<\/p>\n<p><em><img decoding=\"async\" src=\"https:\/\/www.techearth.xyz\/blog\/image.axd?picture=\/Article Images\/json_value2.png\" alt=\"\" \/><\/em><\/p>\n<h4>JSON_QUERY Function<em><br \/>\n<\/em><\/h4>\n<p><em>The <\/em><span class=\"hljs-keyword\">JSON<\/span><span class=\"hljs-variable\">_<\/span><span class=\"hljs-keyword\">QUERY<\/span>(expression [,path]) function takes the name of a variable or a column that contains JSON text and the JSON path that specifies the object or the array to extract as its arguments.<\/p>\n<p><strong>Output<\/strong><\/p>\n<p><img decoding=\"async\" src=\"https:\/\/www.techearth.xyz\/blog\/image.axd?picture=\/Article Images\/json_query.png\" alt=\"\" \/><\/p>\n<p>It returns a JSON fragment of type <em>nvarchar(max)<\/em>. Like the JSON_VALUE function returns null if specified value is not an object or an array.\u00a0 Using\u00a0&#8216;strict&#8217; keyword will throw the error.<\/p>\n<h4>JSON_MODIFY Function<\/h4>\n<p>It updates the value of a property in a JSON string and returns the updated JSON string. It takes expression, path, and new as arguments. Using this function, we can do below operation on a JSON string:<\/p>\n<ol>\n<li>Update<\/li>\n<li>Insert<\/li>\n<li>Delete<\/li>\n<li>Append<\/li>\n<\/ol>\n<h5><span style=\"text-decoration: underline\">1. Update<\/span><\/h5>\n<p>Updates the value of a given path.<\/p>\n<pre>SELECT JSON_MODIFY(@json,'$.info[0].name', 'Mehedi')<\/pre>\n<p><span style=\"text-decoration: underline\">Output<\/span><\/p>\n<p><img decoding=\"async\" src=\"https:\/\/www.techearth.xyz\/blog\/image.axd?picture=\/Article Images\/JM_update.png\" alt=\"\" \/><\/p>\n<h5><span style=\"text-decoration: underline\">2. Insert<\/span><\/h5>\n<p>New value is added in JSON string if the attribute in the provided path does not exist. Otherwise it will update the existing value as per above example. The new attribute is added at the end of the JSON.<\/p>\n<pre>SELECT JSON_MODIFY(@json,'$.info[0].last_name', 'Mehedi')<\/pre>\n<p><span style=\"text-decoration: underline\">Output<\/span><\/p>\n<p><img decoding=\"async\" src=\"https:\/\/www.techearth.xyz\/blog\/image.axd?picture=\/Article Images\/JM_insert.png\" alt=\"\" \/><\/p>\n<h5><span style=\"text-decoration: underline\">3. Delete<\/span><\/h5>\n<p>Putting the NULL value in the path will just remove it.<\/p>\n<pre>SELECT JSON_MODIFY(@json,'$.info[0].name', NULL)<\/pre>\n<p><span style=\"text-decoration: underline\">Output<\/span><\/p>\n<p><img decoding=\"async\" src=\"https:\/\/www.techearth.xyz\/blog\/image.axd?picture=\/Article Images\/JM_deleted.png\" alt=\"\" \/><\/p>\n<h5><span style=\"text-decoration: underline\">4. Append<\/span><\/h5>\n<p>New element can be appended in array like below:<\/p>\n<pre>SELECT JSON_MODIFY(@json,'append $.info[0].credit_card','4539-5385-7425-5825')<\/pre>\n<p><span style=\"text-decoration: underline\">Output<\/span><\/p>\n<p><img decoding=\"async\" src=\"https:\/\/www.techearth.xyz\/blog\/image.axd?picture=\/Article Images\/JM_append.png\" alt=\"\" \/><\/p>\n<h4>OPENJSON Function<\/h4>\n<p>This is a table-valued function that parses JSON text and returns objects and properties from the JSON input as rows and columns.<\/p>\n<pre>DECLARE @json NVARCHAR(MAX);\r\nSET @json =\r\nN'{\"id\":\"1\", \"name\": \"Robert Aragon\", \"ssn\": \"489-36-8350\",\r\n\"credit_card\":[\"4929-3813-3266-4295\",\"5370-4638-8881-3020\"], \"address\":\r\n{\"town\": \"Avon\", \"area\": \"New York\", \"zipcode\": \"76148\"}}';\r\nSELECT * FROM OpenJson(@json)\r\nwith (\r\n\u00a0\u00a0 \u00a0id int\u00a0 '$.id',\r\n\u00a0\u00a0 \u00a0name varchar(50) '$.name',\r\n\u00a0\u00a0 \u00a0ssn varchar(50) '$.ssn',\r\n\u00a0\u00a0 \u00a0[credit_card] nvarchar(MAX)\u00a0 AS JSON,\r\n\u00a0\u00a0 \u00a0[address] nvarchar(MAX)\u00a0 AS JSON\r\n)<\/pre>\n<p><strong>Output<\/strong><\/p>\n<p><img decoding=\"async\" src=\"https:\/\/www.techearth.xyz\/blog\/image.axd?picture=\/Article Images\/JM_openjson.png\" alt=\"\" \/><\/p>\n<h4>FOR JSON Clause<\/h4>\n<p>This clause is widely used with TSQL for exporting SQL table data to JSON format. It has two variants:<\/p>\n<ul>\n<li>AUTO &#8211; Default JSON output is generated using AUTO option.<\/li>\n<li>PATH &#8211; The structure of JSON can be modified by the column name or aliases using PATH option<\/li>\n<\/ul>\n<pre>IF OBJECT_ID('Test1', 'U') IS NOT NULL\r\n    DROP TABLE Test1;\r\nGO\r\n\r\n-- Create the table \r\nCREATE TABLE Test1( \r\n    pk_id    int not null identity(1,1), \r\n    name    varchar(10) default ('Mehedi') \r\n) \r\n GO \r\n -- Populate with 3 sample data \r\n INSERT INTO Test1 default values\r\nGO 2\r\nIF OBJECT_ID('Test2', 'U') IS NOT NULL\r\n    DROP TABLE Test2;\r\nGO\r\n-- Create the table\r\nCREATE TABLE Test2(\r\n    pk_id    int not null identity(1,1),\r\n    area    varchar(10) default ('Dhanmondi'),\r\n    city    varchar(10) default ('Dhaka')\r\n)\r\nGO\r\n-- Populate with 3 sample data\r\nINSERT INTO Test2 default values\r\nGO 1\r\n-- Example of AUTO\r\nSELECT A.pk_id, A.name, (SELECT pk_id, area, city\r\n    FROM Test2 B\r\n    WHERE A.pk_id = B.pk_id\r\n    FOR JSON AUTO\r\n    ) as test2\r\nFROM Test1 A\r\nFOR JSON AUTO\r\n-- Example of PATH\r\nSELECT A.pk_id, A.name, (SELECT pk_id as id, area, city\r\n    FROM Test2 B\r\n    WHERE A.pk_id = B.pk_id\r\n    FOR JSON AUTO\r\n    ) as test2\r\nFROM Test1 A\r\nFOR JSON PATH , ROOT ('EmployeeInfo')\r\nGO\r\n<\/pre>\n<p><strong>Output<\/strong><\/p>\n<p><img decoding=\"async\" src=\"https:\/\/www.techearth.xyz\/blog\/image.axd?picture=\/Article Images\/JM_Auto.png\" alt=\"\" \/><\/p>\n<h4>Conclusion<\/h4>\n<p>This is all about the JSON in SQL Server. Happy TSQLing!<\/p>\n<\/div>\n<p>This article is taken from my <a href=\"https:\/\/www.techearth.xyz\/post\/2020\/08\/25\/json-in-sql-server\" target=\"_blank\" rel=\"noopener noreferrer\">blog<\/a>.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>JSON (JavaScript Object Notation) is very popular standard for exchanging data specially in REST API. Almost all modern websites, android or iOS app use this for exchanging data with server. From SQL Server 2016, Microsoft extends its support for JSON through couple of built-in functions. This way SQL Server supports NOSQL features in traditional relational &hellip;<\/p>\n","protected":false},"author":10927,"featured_media":26001,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"om_disable_all_campaigns":false,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"_uf_show_specific_survey":0,"_uf_disable_surveys":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[2227],"tags":[15805,15806],"class_list":["post-25997","post","type-post","status-publish","format-standard","has-post-thumbnail","","category-sql-server-2","tag-json","tag-sql-server-json"],"aioseo_notices":[],"jetpack_featured_media_url":"https:\/\/ittutorial.org\/wp-content\/uploads\/2020\/10\/Json.png","jetpack_sharing_enabled":true,"amp_enabled":true,"_links":{"self":[{"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/posts\/25997","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/users\/10927"}],"replies":[{"embeddable":true,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/comments?post=25997"}],"version-history":[{"count":4,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/posts\/25997\/revisions"}],"predecessor-version":[{"id":26002,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/posts\/25997\/revisions\/26002"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/media\/26001"}],"wp:attachment":[{"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/media?parent=25997"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/categories?post=25997"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/tags?post=25997"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}