Month: September 2019

Data Sets with JSON

Prior to SQL Server 2016, SQL Server did not provide many options to work with JSON documents. However, JSON has become a favored format for developers to pass data between applications and application layers. This blog post we will take a look at how to build a JSON document and return it in a result set. We are going to build on a previous post where we built a comma separated list of values and returned them in a data set. Here is the result of the previous blog post

StudentID FirstName LastName Courses
10001BobRobertsICS140, ICS225, ICS240, ICS310
10002JohnJohnsonICS240, ICS310
10003TomTompson
10004RichRichardsonICS140, ICS225
10005WillWilliamsICS140

This time we are going to change the output to return a JSON object that will contain the course code, course name and grade the student received. The JSON document structure should look like this:

{
  "Grades": [{
    "CourseCode": "[Course Code]",
    "CourseName": "[Course Name]",
    "Grade": "[Course Grade]"
  }]
}

Before we dig into this blog a brief background regarding JSON (JavaScript Object Notation) may help. JSON is a lightweight data interchange format similar to XML, however it is based on programing languages constructs derived from languages such as C and Java. It is a data format that is easily understandable for both humans and machines, making it ideal for transferring data between applications. JSON is machine and language independent, which makes it a great language for data interchange between different systems. For more information regarding JSON please see the following URL https://www.json.org/json-en.html.

We have one minor tweak to the table structure from the previous blog post where we add the grade to the Student Course weak entity table

CREATE TABLE #StudentCourse(
    StudentID INT NOT NULL
  , CourseID INT NOT NULL
  , Grade VARCHAR(5) NULL
);
GO
 
INSERT INTO #StudentCourse (StudentID, CourseID, Grade)
VALUES (10001, 140, 'A')
     , (10001, 225, 'B+')
     , (10001, 240, 'B-')
     , (10001, 310, 'B')
     , (10002, 240, 'C+')
     , (10002, 310, 'A-')
     , (10004, 140, 'A')
     , (10004, 225, 'B')
     , (10005, 140, 'A');

What we want the script to return is a result set with student details including a JSON document with course and grade information which will look like this:

StudentID FirstName LastName Courses
10001BobRoberts{“Grades”:[{“CourseCode”:”ICS140″, “CourseName”:”Introduction to Programming”, “Grade”:”A”}, {“CourseCode”:”ICS225″, “CourseName”:”Web Programming”, “Grade”:”B+”}, {“CourseCode”:”ICS240″, “CourseName”:”Advanced Programming”, “Grade”:”B-“}, {“CourseCode”:”ICS310″, “CourseName”:”Database Management”, “Grade”:”B”}]}
10002JohnJohnson{“Grades”:[{“CourseCode”:”ICS240″, “CourseName”:”Advanced Programming”, “Grade”:”C+”}, {“CourseCode”:”ICS310″, “CourseName”:”Database Management”, “Grade”:”A-“}]}
10003TomTompson
10004RichRichardson{“Grades”:[{“CourseCode”:”ICS140″, “CourseName”:”Introduction to Programming”, “Grade”:”A”}, {“CourseCode”:”ICS225″, “CourseName”:”Web Programming”, “Grade”:”B”}]}
10005WillWilliams{“Grades”:[{“CourseCode”:”ICS140″, “CourseName”:”Introduction to Programming”, “Grade”:”A”}]}

We will reuse most of the query and simply add some additional string concatenation. The previous sub query just concatenated a comma to the course code, this time we are going to build repeating JSON objects within the array.

{"CourseCode": "[Course Code]","CourseName": "[Course Name]","Grade": "[Course Grade]"}
-- CSV Version
SELECT ', ' + sc.Grade 
FROM #StudentCourse sc JOIN #Courses c ON c.CourseID = sc.CourseID
WHERE s.StudentID = sc.StudentID
FOR XML PATH('')
 
-- New JSON Version
SELECT ', {"CourseCode":"'+ c.CourseCode + '", "CourseName":"' + c.CourseName + '", "Grade":"' + sc.Grade + '"}' 
FROM #StudentCourse sc JOIN #Courses c ON c.CourseID = sc.CourseID
WHERE s.StudentID = sc.StudentID
FOR XML PATH('')

Now that we have the repeating JSON objects within the array, we need to build out the top object in the document and the array structure. We can accomplish this by adding additional string concatenation in the outer portion of the query.

SELECT s.StudentID
	 , s.FirstName
	 , s.LastName
	 , GradesJSON = '{"Grades":[' + SUBSTRING((
			SELECT ', {"CourseCode":"'+ c.CourseCode + '", "CourseName":"' + c.CourseName + '", "Grade":"' + sc.Grade + '"}' 
			FROM #StudentCourse sc JOIN #Courses c ON c.CourseID = sc.CourseID
			WHERE s.StudentID = sc.StudentID
			FOR XML PATH('')),3,1000) + ']}'
FROM #Students s;

Here is the full script:

DROP TABLE IF EXISTS #Students
GO
 
DROP TABLE IF EXISTS #Courses
GO
 
DROP TABLE IF EXISTS #StudentCourse
GO
 
CREATE TABLE #Students (
   StudentID INT PRIMARY KEY NOT NULL
 , FirstName NVARCHAR(50) NOT NULL
 , LastName NVARCHAR(50) NOT NULL
 )
GO
 
CREATE TABLE #Courses (
	CourseID INT PRIMARY KEY NOT NULL
  , CourseCode NVARCHAR(50) NOT NULL
  , CourseName NVARCHAR(50) NOT NULL
)
 
CREATE TABLE #StudentCourse(
	StudentID INT NOT NULL
  , CourseID INT NOT NULL
  , Grade VARCHAR(5) NULL
)
 
INSERT   INTO #Students
         (StudentID, FirstName, LastName)
VALUES   (10001, N'Bob',N'Roberts')
	   , (10002, N'John',N'Johnson')
	   , (10003, N'Tom',N'Tompson')
	   , (10004, N'Rich',N'Richardson')
	   , (10005, N'Will',N'Williams');
GO
 
INSERT INTO #Courses (
    CourseID
  , CourseCode
  , CourseName
)
VALUES (140, N'ICS140', N'Introduction to Programming')
     , (225, N'ICS225', N'Web Programming')
     , (240, N'ICS240', N'Advanced Programming')
     , (310, N'ICS310', N'Database Management');
 
INSERT INTO #StudentCourse (StudentID, CourseID, Grade)
VALUES (10001, 140, 'A')
     , (10001, 225, 'B+')
     , (10001, 240, 'B-')
     , (10001, 310, 'B')
     , (10002, 240, 'C+')
     , (10002, 310, 'A-')
     , (10004, 140, 'A')
     , (10004, 225, 'B')
     , (10005, 140, 'A');
 
 
SELECT s.StudentID
	 , s.FirstName
	 , s.LastName
	 , GradesJSON = '{"Grades":[' + SUBSTRING((
			SELECT ', {"CourseCode":"'+ c.CourseCode + '", "CourseName":"' + c.CourseName + '", "Grade":"' + sc.Grade + '"}' 
			FROM #StudentCourse sc JOIN #Courses c ON c.CourseID = sc.CourseID
			WHERE s.StudentID = sc.StudentID
			FOR XML PATH('')),3,1000) + ']}'
FROM #Students s;

In a future post I will show how to build this Data Set using JSON functions in SQL 2016.