{"id":629,"date":"2019-09-06T10:50:21","date_gmt":"2019-09-06T05:20:21","guid":{"rendered":"https:\/\/techieshouts.com\/?p=629"},"modified":"2022-08-09T19:06:17","modified_gmt":"2022-08-09T13:36:17","slug":"openjson-function-in-sql-server","status":"publish","type":"post","link":"https:\/\/techieshouts.com\/home\/openjson-function-in-sql-server\/","title":{"rendered":"OPENJSON function in SQL Server"},"content":{"rendered":"\n<p>In the <a href=\"https:\/\/techieshouts.com\/import-json-files-into-sql-server\/\">previous article<\/a>, I have explained about different ways to import JSON into SQL server. One of the ways is using OPENJSON function. In this article, I will explain OPENJSON function in SQL Server with some examples.<\/p>\n\n\n\n<p style=\"text-align:left\">OPENJSON is a table valued function that parses JSON document and transforms it into rows and columns. This function is introduced in SQL server 2016 version.<\/p>\n\n\n\n<h2>Syntax<\/h2>\n\n\n\n<pre data-mode=\"sqlserver\" data-theme=\"dreamweaver\" data-fontsize=\"14\" data-lines=\"Infinity\" class=\"wp-block-simple-code-block-ace\">OPENJSON( jsonExpression [ , jsonPath ] ) \n[ \n    WITH ( col1_Name type [column1_path] [AS JSON],\n    [colN_Name type [columnN_path] [AS JSON]] ) \n]<\/pre>\n\n\n\n<h2>Parameters<\/h2>\n\n\n\n<ul><li>jsonExpression &#8211; It is the input JSON text to transform it into tabular format.<\/li><li>jsonPath- It is an optional expression that refers to an array or object in the JSON text.<\/li><li>WITH Clause &#8211; It is an optional clause to define the output schema explicitly. It contains colName, type, JSON path and [AS JSON] elements.<\/li><li>[AS JSON] &#8211; This option is used to return the object or array from the input text as JSON. <\/li><\/ul>\n\n\n\n<p>Now, let&#8217;s see some of the examples by using default schema and explicit schema methods<\/p>\n\n\n\n<h2>Examples<\/h2>\n\n\n\n<p><strong>Example 1:<\/strong> This example describes the default schema method with all possible types.<\/p>\n\n\n\n<pre data-mode=\"sqlserver\" data-theme=\"dreamweaver\" data-fontsize=\"14\" data-lines=\"Infinity\" class=\"wp-block-simple-code-block-ace\">DECLARE @JSON NVARCHAR(1000) = N'\n    {\"Initial\": null, \"EmpName\":\"Sean Paul\", \"EmpID\":101, \"IsPermanent\": false, \"Skills\":[\"SQL\",\"C#.NET\",\"VB\"], \n\t\"Address\":{\"Address1\":\"103 Greensboro Street\",\"State\":\"MS\", \"Country\":\"USA\"}}'\n\n--Default schema\nSELECT * FROM OPENJSON(@JSON)<\/pre>\n\n\n\n<p>Below is the result of the above query. It returns three columns &#8211; key, value and the type of each key-value pair. The number in the type column denotes the data type of the value column. It ranges from 0 through 5.<\/p>\n\n\n\n<div class=\"wp-block-image\"><figure class=\"aligncenter\"><img loading=\"lazy\" width=\"591\" height=\"171\" src=\"https:\/\/techieshouts.com\/wp-content\/uploads\/2019\/09\/OpenJSON-Ex1.png\" alt=\"OPENJSON default schema with all possible types\" class=\"wp-image-630\" srcset=\"https:\/\/techieshouts.com\/wp-content\/uploads\/2019\/09\/OpenJSON-Ex1.png 591w, https:\/\/techieshouts.com\/wp-content\/uploads\/2019\/09\/OpenJSON-Ex1-300x87.png 300w\" sizes=\"(max-width: 591px) 100vw, 591px\" \/><\/figure><\/div>\n\n\n\n<p><strong>Example 2:<\/strong> This example explains how to access JSON array and object by specifying &#8216;jsonPath&#8217; with default schema.<\/p>\n\n\n\n<pre data-mode=\"sqlserver\" data-theme=\"dreamweaver\" data-fontsize=\"14\" data-lines=\"Infinity\" class=\"wp-block-simple-code-block-ace\">DECLARE @JSON NVARCHAR(1000) = N'\n    {\"Initial\": null, \"EmpName\":\"Sean Paul\", \"EmpID\":101, \"IsPermanent\": false, \"Skills\":[\"SQL\",\"C#.NET\",\"VB\"], \n\t\"Address\":{\"Address1\":\"103 Greensboro Street\",\"State\":\"MS\", \"Country\":\"USA\"}}'\n\nSELECT * FROM OPENJSON(@JSON, '$.Skills')\nSELECT * FROM OPENJSON(@JSON, '$.Address')<\/pre>\n\n\n\n<div class=\"wp-block-image\"><figure class=\"aligncenter\"><img loading=\"lazy\" width=\"291\" height=\"249\" src=\"https:\/\/techieshouts.com\/wp-content\/uploads\/2019\/09\/OpenJSON-Ex2.png\" alt=\"OPENJSON with json_path option\" class=\"wp-image-631\"\/><\/figure><\/div>\n\n\n\n<p><strong>Example 3:<\/strong> In this example, let&#8217;s see the usage of explicit schema method.<\/p>\n\n\n\n<pre data-mode=\"sqlserver\" data-theme=\"dreamweaver\" data-fontsize=\"14\" data-lines=\"Infinity\" class=\"wp-block-simple-code-block-ace\">DECLARE @JSON NVARCHAR(1000) = N'\n    {\"Initial\": null, \"EmpName\":\"Sean Paul\", \"EmpID\":101, \"IsPermanent\": false, \"Skills\":[\"SQL\",\"C#.NET\",\"VB\"], \n\t\"Address\":{\"Address1\":\"103 Greensboro Street\",\"State\":\"MS\", \"Country\":\"USA\"}}'\n\n--Explicit schema\nSELECT * FROM OPENJSON(@JSON)\nWITH(EmpID INT, EmpName VARCHAR(25), IsPermanent BIT)<\/pre>\n\n\n\n<p>The above example returns the columns specified in the WITH clause. The name of the column should match with the name of the property in the JSON text. If the column name differs, then it returns NULL.<\/p>\n\n\n\n<div class=\"wp-block-image\"><figure class=\"aligncenter\"><img loading=\"lazy\" width=\"245\" height=\"55\" src=\"https:\/\/techieshouts.com\/wp-content\/uploads\/2019\/09\/OpenJSON-Ex3.png\" alt=\"OPENJSON with Explicit schema method\" class=\"wp-image-632\"\/><\/figure><\/div>\n\n\n\n<p><strong>Example 4:<\/strong> This example explains renaming the column, retrieving array and objects with explicit schema.<\/p>\n\n\n\n<pre data-mode=\"sqlserver\" data-theme=\"dreamweaver\" data-fontsize=\"14\" data-lines=\"Infinity\" class=\"wp-block-simple-code-block-ace\">DECLARE @JSON NVARCHAR(1000) = N'\n    {\"Initial\": null, \"EmpName\":\"Sean Paul\", \"EmpID\":101, \"IsPermanent\": false, \"Skills\":[\"SQL\",\"C#.NET\",\"VB\"], \n\t\"Address\":{\"Address1\":\"103 Greensboro Street\",\"State\":\"MS\", \"Country\":\"USA\"}}'\n\nSELECT * FROM OPENJSON(@JSON)\nWITH(EmployeeID INT '$.EmpID', EmployeeName VARCHAR(25) '$.EmpName', Skills VARCHAR(100) '$.Skills[0]', Address1 VARCHAR(50) '$.Address.Address1')<\/pre>\n\n\n\n<p>With explicit schema method, you can rename the column name however you want. In the above example, column &#8216;EmpID&#8217; is renamed as &#8216;EmployeeID&#8217;. Also, you can specify &#8216;jsonPath&#8217; to access arrays and objects in the JSON text. <\/p>\n\n\n\n<div class=\"wp-block-image\"><figure class=\"aligncenter\"><img loading=\"lazy\" width=\"416\" height=\"52\" src=\"https:\/\/techieshouts.com\/wp-content\/uploads\/2019\/09\/OpenJSON-Ex4.png\" alt=\"Accessing inner JSON objects using explicit schema method\" class=\"wp-image-633\" srcset=\"https:\/\/techieshouts.com\/wp-content\/uploads\/2019\/09\/OpenJSON-Ex4.png 416w, https:\/\/techieshouts.com\/wp-content\/uploads\/2019\/09\/OpenJSON-Ex4-300x38.png 300w\" sizes=\"(max-width: 416px) 100vw, 416px\" \/><\/figure><\/div>\n\n\n\n<p><strong>Example 5:<\/strong> Usage of AS JSON option<\/p>\n\n\n\n<pre data-mode=\"sqlserver\" data-theme=\"dreamweaver\" data-fontsize=\"14\" data-lines=\"Infinity\" class=\"wp-block-simple-code-block-ace\">DECLARE @JSON NVARCHAR(1000) = N'\n    {\"Initial\": null, \"EmpName\":\"Sean Paul\", \"EmpID\":101, \"IsPermanent\": false, \"Skills\":[\"SQL\",\"C#.NET\",\"VB\"], \n\t\"Address\":{\"Address1\":\"103 Greensboro Street\",\"State\":\"MS\", \"Country\":\"USA\"}}'\n\nSELECT * FROM OPENJSON(@JSON)\nWITH(EmployeeID INT '$.EmpID', EmployeeName VARCHAR(25) '$.EmpName', Skills NVARCHAR(MAX) '$.Skills' AS JSON, AddressObj NVARCHAR(MAX) '$.Address'  AS JSON)<\/pre>\n\n\n\n<div class=\"wp-block-image\"><figure class=\"aligncenter\"><img loading=\"lazy\" width=\"818\" height=\"53\" src=\"https:\/\/techieshouts.com\/wp-content\/uploads\/2019\/09\/OpenJSON-Ex5.png\" alt=\"AS JSON option returns the inner JSON objects from JSON text\" class=\"wp-image-634\" srcset=\"https:\/\/techieshouts.com\/wp-content\/uploads\/2019\/09\/OpenJSON-Ex5.png 818w, https:\/\/techieshouts.com\/wp-content\/uploads\/2019\/09\/OpenJSON-Ex5-300x19.png 300w, https:\/\/techieshouts.com\/wp-content\/uploads\/2019\/09\/OpenJSON-Ex5-768x50.png 768w, https:\/\/techieshouts.com\/wp-content\/uploads\/2019\/09\/OpenJSON-Ex5-660x43.png 660w\" sizes=\"(max-width: 818px) 100vw, 818px\" \/><\/figure><\/div>\n\n\n\n<p>You can use AS JSON option in the column definition to return a nested array or object from a JSON text. The type of the column must be specified as NVARCHAR(MAX).<\/p>\n\n\n\n<p><strong>Example 6:<\/strong> In this example, let&#8217;s see how to retrieve data from array of JSON objects.<\/p>\n\n\n\n<pre data-mode=\"sqlserver\" data-theme=\"dreamweaver\" data-fontsize=\"14\" data-lines=\"Infinity\" class=\"wp-block-simple-code-block-ace\">DECLARE @JSON NVARCHAR(1000) = N'\n    {\"Employee\": \n\t[{\"Initial\": null, \"EmpName\":\"Sean Paul\", \"EmpID\":101, \"IsPermanent\": false, \"Skills\":[\"SQL\",\"C#.NET\",\"VB\"], \n\t\"Address\":{\"Address1\":\"103 Greensboro Street\",\"State\":\"MS\", \"Country\":\"USA\"}},\n\t{\"Initial\": \"A\", \"EmpName\":\"Peter Frank\", \"EmpID\":201, \"IsPermanent\": true, \"Skills\":[\"MongoDB\",\"PHP\"], \n\t\"Address\":{\"Address1\":\"50 Lakes Street\",\"State\":\"CA\", \"Country\":\"USA\"}}\n\t]}'\n\nSELECT * FROM OPENJSON(@JSON, '$.Employee')\nWITH(EmployeeID INT '$.EmpID', EmployeeName VARCHAR(25) '$.EmpName', [State] VARCHAR(50) '$.Address.State', Skills NVARCHAR(MAX) AS JSON)<\/pre>\n\n\n\n<div class=\"wp-block-image\"><figure class=\"aligncenter\"><img loading=\"lazy\" width=\"427\" height=\"75\" src=\"https:\/\/techieshouts.com\/wp-content\/uploads\/2019\/09\/OpenJSON-Ex6.png\" alt=\"Results from array of JSON objects\" class=\"wp-image-635\" srcset=\"https:\/\/techieshouts.com\/wp-content\/uploads\/2019\/09\/OpenJSON-Ex6.png 427w, https:\/\/techieshouts.com\/wp-content\/uploads\/2019\/09\/OpenJSON-Ex6-300x53.png 300w\" sizes=\"(max-width: 427px) 100vw, 427px\" \/><\/figure><\/div>\n\n\n\n<p>Refer <a rel=\"noreferrer noopener\" aria-label=\"OPENJSON (T-SQL) (opens in a new tab)\" href=\"https:\/\/docs.microsoft.com\/en-us\/sql\/t-sql\/functions\/openjson-transact-sql?view=sql-server-2017\" target=\"_blank\">OPENJSON (T-SQL)<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"<p>In the previous article, I have explained about different ways to import JSON into SQL server. One of the ways is using OPENJSON function. In this article, I will explain OPENJSON function in SQL Server with some examples. OPENJSON is a table valued function that parses JSON document and transforms it into rows and columns.\u2026 <span class=\"read-more\"><a href=\"https:\/\/techieshouts.com\/home\/openjson-function-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,15],"tags":[58,39,61,62,63],"_links":{"self":[{"href":"https:\/\/techieshouts.com\/home\/wp-json\/wp\/v2\/posts\/629"}],"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=629"}],"version-history":[{"count":3,"href":"https:\/\/techieshouts.com\/home\/wp-json\/wp\/v2\/posts\/629\/revisions"}],"predecessor-version":[{"id":638,"href":"https:\/\/techieshouts.com\/home\/wp-json\/wp\/v2\/posts\/629\/revisions\/638"}],"wp:attachment":[{"href":"https:\/\/techieshouts.com\/home\/wp-json\/wp\/v2\/media?parent=629"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/techieshouts.com\/home\/wp-json\/wp\/v2\/categories?post=629"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/techieshouts.com\/home\/wp-json\/wp\/v2\/tags?post=629"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}