Format JSON Output in SQL Server

Introduction

From SQL Server 2016 JSON functions are introduced, since many modern Web and Mobile applications are using JSON data format for exchanging data. Many REST web service results are in format of JSON text or accept the data that is formatted as JSON. In this article, we will have a look How to Create or Format JSON text using relational data in SQL Server.

Disclaimer – The views/contents shared/authored in this article are mine and not of my employer. The table designs are JUST to convey the concepts and are not efficient ones.

Format JSON Output

The majorly used Format JSON text from SQL Server tables are

Auto Mode
Path Mode

Auto Mode

Auto mode, automatically formats JSON text, based on the SELECT Statements. All we need to specify the AUTO option at the end. Based on the order of columns in the SELECT statement the format of JSON is automatically determined. We cannot take the control to maintain the output.

To use FOR AUTO option in the select statement must have FROM Clause in the query.

Syntax

SELECT COLUMN_1, COLUMN_2 ......COLUMN_N FROM TABLE_NAME 
FOR JSON AUTO

Example

For this example, lets consider three tables and its columns needs to be formatted in JSON text in SQL Server. The tables are as follows,


SELECT * FROM  Details.StudentDetails

SELECT * FROM  Details.StudentCourseDetails 

SELECT * FROM  Details.StudentPersonalDetails

When we run the about T-SQL, we get the table results as,

These are the table result columns and normally we apply join(s) and fetch the data.

SELECT 
	SD.StudentId, 
	SD.FirstName,
	SD.LastName, 
	SD.RegistrationNumber, 
	SD.Degree, 
	SCD.CourseName, 
	SCD.NoOfSemester, 
	SCD.CurrentSemester, 
	SCD.HODName, 
	SPD.Gender, 
	SPD.AddressLine1,
	SPD.AddressLine2, 
	SPD.ZipCode,
	SPD.Country, 
	SPD.MobileNumber
FROM 
     Details.StudentDetails AS SD
JOIN Details.StudentCourseDetails AS SCD ON      
               SCD.StudentId=SD.StudentId
JOIN Details.StudentPersonalDetails  AS SPD ON 
               SPD.StudentId=SD.StudentId

This is the how normally we get the results from SELECT statement and then we pass it to our .NET or Java or any other stack applications, and in that programming language we Parse data into JSON format. But by applying the FOR JSON AUTO option, we can Format the data into JSON in SQL itself.

SELECT 
	SD.StudentId, 
	SD.FirstName,
	SD.LastName, 
	SD.RegistrationNumber, 
	SD.Degree, 
	SCD.CourseName, 
	SCD.NoOfSemester, 
	SCD.CurrentSemester, 
	SCD.HODName, 
	SPD.Gender, 
	SPD.AddressLine1,
	SPD.AddressLine2, 
	SPD.ZipCode,
	SPD.Country, 
	SPD.MobileNumber
FROM 
     Details.StudentDetails AS SD
JOIN Details.StudentCourseDetails AS SCD ON 
             SCD.StudentId=SD.StudentId
JOIN Details.StudentPersonalDetails  AS SPD ON 
             SPD.StudentId=SD.StudentId
FOR JSON AUTO

When we execute the above code, we will get result in JSON format. Lets have a look how the JSON text looks. In SQL Server, there is no option to view the JSON text. We need to use other options to view the JSON.

[
   {
      "StudentId":1,
      "FirstName":"Sundaram",
      "LastName":"Subramanian",
      "RegistrationNumber":789456123,
      "Degree":"PG",
      "SCD":[
         {
            "CourseName":"Masters of Computer Applications",
            "NoOfSemester":6,
            "CurrentSemester":4,
            "HODName":"Dr.Praba",
            "SPD":[
               {
                  "Gender":"Male",
                  "AddressLine1":"Street1",
                  "AddressLine2":"Tamil Nadu",
                  "ZipCode":641001,
                  "Country":"India",
                  "MobileNumber":1234567890
               }
            ]
         }
      ]
   },
   {
      "StudentId":2,
      "FirstName":"Saravana",
      "LastName":"Kumar",
      "RegistrationNumber":789456124,
      "Degree":"PG",
      "SCD":[
         {
            "CourseName":"Masters of Computer Applications",
            "NoOfSemester":6,
            "CurrentSemester":4,
            "HODName":"Dr.Praba",
            "SPD":[
               {
                  "Gender":"Male",
                  "AddressLine1":"Street2",
                  "AddressLine2":"Tamil Nadu",
                  "ZipCode":641001,
                  "Country":"India",
                  "MobileNumber":2134567890
               }
            ]
         }
      ]
   }
]

Path Mode

In Auto mode, we cannot take the control to maintain the Output, but by using FOR JSON PATH we can maintain the output and also we can create wrapper object and complex properties. The results are formatted as an array of JSON objects. The FOR JSON PATH clause uses the column alias or column name to determine the key name in the JSON output. If an alias contains dots, the PATH option creates nested objects.

Syntax

SELECT COLUMN_1, COLUMN_2 ......COLUMN_N FROM TABLE_NAME 
FOR JSON PATH

Here is our SELECT statement with Alias names contains with Dots.

SELECT TOP 2 
	SD.StudentId, 
	SD.FirstName AS 'FullName.FirstName',
	SD.LastName AS 'FullName.LastName', 
	SD.RegistrationNumber AS  
        'DepartmentDetails.RegistrationNumber', 
	SD.Degree AS 'Department.Degree', 
	SCD.CourseName AS 'Department.Course', 
	SCD.NoOfSemester AS 'Department.TotalSemesters', 
	SCD.CurrentSemester AS 'Department.CurrentSemester', 
	SCD.HODName AS 'Department.HOD' , 
	SPD.Gender AS 'PersonalDetails.Gender' , 
	SPD.AddressLine1 AS 'PersonalDetails.StreetName',
	SPD.AddressLine2 AS 'PersonalDetails.Optional', 
	SPD.ZipCode AS 'PersonalDetails.ZipCode',
	SPD.Country AS 'PersonalDetails.Country', 
	SPD.MobileNumber AS 'PersonalDetails.ContactNumber'
FROM 
     Details.StudentDetails AS SD
JOIN Details.StudentCourseDetails AS SCD ON 
             SCD.StudentId=SD.StudentId
JOIN Details.StudentPersonalDetails  AS SPD ON 
             SPD.StudentId=SD.StudentId 
FOR JSON PATH

When the above SELECT statement is ran, we get result in the SQL as,

Now lets copy this result and view in using any JSON Format tool, and the JSON text looks like as shown below,

[
   {
      "StudentId":1,
      "FullName":{
         "FirstName":"Sundaram",
         "LastName":"Subramanian"
      },
      "DepartmentDetails":{
         "RegistrationNumber":789456123
      },
      "Department":{
         "Degree":"PG",
         "Course":"Masters of Computer Applications",
         "TotalSemesters":6,
         "CurrentSemester":4,
         "HOD":"Dr.Praba"
      },
      "PersonalDetails":{
         "Gender":"Male",
         "StreetName":"Street1",
         "Optional":"Tamil Nadu",
         "ZipCode":641001,
         "Country":"India",
         "ContactNumber":1234567890
      }
   },
   {
      "StudentId":2,
      "FullName":{
         "FirstName":"Saravana",
         "LastName":"Kumar"
      },
      "DepartmentDetails":{
         "RegistrationNumber":789456124
      },
      "Department":{
         "Degree":"PG",
         "Course":"Masters of Computer Applications",
         "TotalSemesters":6,
         "CurrentSemester":4,
         "HOD":"Dr.Praba"
      },
      "PersonalDetails":{
         "Gender":"Male",
         "StreetName":"Street2",
         "Optional":"Tamil Nadu",
         "ZipCode":641001,
         "Country":"India",
         "ContactNumber":2134567890
      }
   }
]

Adding ROOT Node

We can also add root node for the JSON text. By adding the ROOT() option after the FOR JSON PATH.

SELECT TOP 2 
	SD.StudentId, 

	SD.FirstName AS 'FullName.FirstName',
	SD.LastName AS 'FullName.LastName', 
	SD.RegistrationNumber AS 'DepartmentDetails.RegistrationNumber', 
	SD.Degree AS 'Department.Degree', 
	SCD.CourseName AS 'Department.Course', 
	SCD.NoOfSemester AS 'Department.TotalSemesters', 
	SCD.CurrentSemester AS 'Department.CurrentSemester', 
	SCD.HODName AS 'Department.HOD' , 
	SPD.Gender AS 'PersonalDetails.Gender' , 
	SPD.AddressLine1 AS 'PersonalDetails.StreetName',
	SPD.AddressLine2 AS 'PersonalDetails.Optional', 
	SPD.ZipCode AS 'PersonalDetails.ZipCode',
	SPD.Country AS 'PersonalDetails.Country', 
	SPD.MobileNumber AS 'PersonalDetails.ContactNumber'
FROM 
	Details.StudentDetails AS SD
JOIN Details.StudentCourseDetails AS SCD ON SCD.StudentId=SD.StudentId
JOIN Details.StudentPersonalDetails  AS SPD ON SPD.StudentId=SD.StudentId 
FOR JSON PATH,  ROOT('StudentDetails')

And the result JSON text with Root node looks like,

{
   "StudentDetails":[
      {
         "StudentId":1,
         "FullName":{
            "FirstName":"Sundaram",
            "LastName":"Subramanian"
         },
         "DepartmentDetails":{
            "RegistrationNumber":789456123
         },
         "Department":{
            "Degree":"PG",
            "Course":"Masters of Computer Applications",
            "TotalSemesters":6,
            "CurrentSemester":4,
            "HOD":"Dr.Praba"
         },
         "PersonalDetails":{
            "Gender":"Male",
            "StreetName":"Street1",
            "Optional":"Tamil Nadu",
            "ZipCode":641001,
            "Country":"India",
            "ContactNumber":1234567890
         }
      },
      {
         "StudentId":2,
         "FullName":{
            "FirstName":"Saravana",
            "LastName":"Kumar"
         },
         "DepartmentDetails":{
            "RegistrationNumber":789456124
         },
         "Department":{
            "Degree":"PG",
            "Course":"Masters of Computer Applications",
            "TotalSemesters":6,
            "CurrentSemester":4,
            "HOD":"Dr.Praba"
         },
         "PersonalDetails":{
            "Gender":"Male",
            "StreetName":"Street2",
            "Optional":"Tamil Nadu",
            "ZipCode":641001,
            "Country":"India",
            "ContactNumber":2134567890
         }
      }
   ]
}

Conclusion

In this article, we have discussed how to format or create JSON data from SQL Server. I hope you all found this much useful. Kindly share this article with your networks and share your feedback in the comment section.

Introduction

Format JSON Output

Auto Mode

Syntax

Example

Path Mode

Syntax

Adding ROOT Node

Conclusion

Share this:

Like this:

Related

Leave a Reply Cancel reply