SQL Server - Convert JSON data into relational data (rows & columns) format

In my previous post, I shared some examples on Converting relational data to JSON data in SQL Server.


In this post, let us see examples for converting JSON data into relational data format.

For Example: Consider below JSON data :




'{"MSSQL":[
    {
    "SQLServer":{  
   "Version":"2008",
   "CompatibilityLevel":100,
   "CodeName":"Katmai" },
    "YearofRelease":"2008"
    },
 {
    "SQLServer":{  
   "Version":"2012",
   "CompatibilityLevel":110,
   "CodeName":"Denali" },
    "YearofRelease":"2012"
    },
 {   
    "SQLServer":{  
   "Version":"2014",
   "CompatibilityLevel":120,
   "CodeName":"Hekaton" },
    "YearofRelease":"2014"
    },
 {
    "SQLServer":{  
   "Version":"2016",
   "CompatibilityLevel":130,
   "CodeName":"2016" },
    "YearofRelease":"2016"
    }
   ]
}'

Let us check if this JSON is valid


DECLARE @json_document NVARCHAR(MAX)=
'{"MSSQL":[
    {
    "SQLServer":{  
   "Version":"2008",
   "CompatibilityLevel":100,
   "CodeName":"Katmai" },
    "YearofRelease":"2008"
    },
 {
    "SQLServer":{  
   "Version":"2012",
   "CompatibilityLevel":110,
   "CodeName":"Denali" },
    "YearofRelease":"2012"
    },
 {   
    "SQLServer":{  
   "Version":"2014",
   "CompatibilityLevel":120,
   "CodeName":"Hekaton" },
    "YearofRelease":"2014"
    },
 {
    "SQLServer":{  
   "Version":"2016",
   "CompatibilityLevel":130,
   "CodeName":"2016" },
    "YearofRelease":"2016"
    }
   ]
}'

SELECT ISJSON(@json_document) Valid_Json

1 = valid
0 = Invalid



I am going to insert this valid JSON data in to SQL Server table.

Note that there is no specific data type introduced till date to hold JSON data like we have XML data type for storing XML data.



Using OPENJSON, we can shred JSON data into rows & Columns format.
By writing path expression we can traverse through JSON object

--Returns entire JSON column data
SELECT T.Id,C.[Key],C.Value 
FROM JSON_Data_Tbl T
CROSS APPLY OPENJSON(JSON_Col) C


--Using $ dollar symbol separated by dots we can traverse through JSON object
SELECT T.Id,C.[Key],C.Value 
FROM JSON_Data_Tbl T
CROSS APPLY OPENJSON(JSON_Col,'$') C


--Moving one level down from object MSSQL to next level
SELECT T.Id,C.[Key],C.Value 
FROM JSON_Data_Tbl T
CROSS APPLY OPENJSON(JSON_Col,'$.MSSQL') C



--Path name is case sensitive
SELECT T.Id,C.[Key],C.Value 
FROM JSON_Data_Tbl T
CROSS APPLY OPENJSON(JSON_Col,'$.mssql') C


--Using WITH clause we can shred JSON with explicit schema
--Similar to WITH RESULT SETS clause introduced in SQL Server 2012
SELECT C.* 
FROM JSON_Data_Tbl T
CROSS APPLY OPENJSON(JSON_Col,'$.MSSQL') 
     WITH (
             Version   VARCHAR(50) '$.SQLServer.Version', 
             CompatibilityLevel     VARCHAR(50)     '$.SQLServer.CompatibilityLevel',
             CodeName VARCHAR(50) '$.SQLServer.CodeName',
    YearofRelease VARCHAR(10) '$.YearofRelease'
          )
 AS C;



By adding INSERT statement on top of SELECT with OPENJSON, We can insert JSON data converted into rows &Columns format.


See Also:

No comments: