How to return JSON objects in tabular format using T-SQL?

Question

I am storing student data by group (or class) and week in the following format:

enter image description here

where GroupData is an array of students (relevant fields are PersonId, StudentName, and GradePoints). GradePoints can change from week to week, so I am trying to come up with a way to display the results by group as follows:

enter image description here

where I can see the grades by week side by side.

The solution I have come up so far is not very elegant:

DECLARE @json VARCHAR(MAX) = (SELECT GroupData FROM Groups WHERE Id = 1)
drop table #week1
drop table #week2
SELECT * 
into #Week1
FROM OPENJSON(@json)
WITH (
 StudentId INT '$.PersonId',
 StudentName VARCHAR(100) '$.StudentName',
 Week1 FLOAT '$.GradePoints'
)
DECLARE @json2 VARCHAR(MAX) = (SELECT GroupData FROM Groups WHERE Id = 2)
SELECT * 
into #Week2
FROM OPENJSON(@json2)
WITH (
 StudentId INT '$.PersonId',
 StudentName VARCHAR(100) '$.StudentName',
 Week2 FLOAT '$.GradePoints'
)
select w1.studentid, w1.studentname, w1.week1, w2.week2
from #week1 w1
FULL join #week2 w2
on w1.studentid = w2.studentid

Besides not being elegant, this approach is not scalable for a 14-week course.

If anyone can think of a better solution (even if it requires a change in how the data is stored), I am open to suggestions.

Thanks in advance.

• Ronen Ariely: Please post queries to create the the table with the relevant indexes and insert sample data. According to the sample data please explain what is the requested result which you want to get

Answer 1

You don't need to split the data out into separate tables and re-join for this. Simply use OPENJSON to decompose the JSON into a table and use PIVOT to shift the grade point data into columns.

The below example code can be seen in action here.

Setup example data:

CREATE TABLE DataTable
(
 Id INT,
 GroupId INT,
 Week VARCHAR(25),
 GroupData VARCHAR(MAX)
)
INSERT INTO DataTable
VALUES (1, 3840, 'Week 1', '[ { "StudentName": "Barry", "GradePoints": 100 }, { "StudentName": "Kelly", "GradePoints": 98 }, { "StudentName": "Jenny", "GradePoints": 100 } ]'),
 (2, 3840, 'Week 2', '[ { "StudentName": "Barry", "GradePoints": 95 }, { "StudentName": "Kelly", "GradePoints": 87 }, { "StudentName": "Jenny", "GradePoints": 99 } ]')

Query the data:

SELECT
 GroupId, StudentName, p.[Week 1], p.[Week 2]
FROM
(
 SELECT GroupId, Week, StudentName, GradePoints
 FROM DataTable
 CROSS APPLY OPENJSON ([GroupData])
 WITH (
 StudentName VARCHAR(25) '$.StudentName',
 GradePoints VARCHAR(25) '$.GradePoints'
 )
) src
PIVOT
(
 MAX(GradePoints) FOR [Week] IN ([Week 1], [Week 2])
) p

Results:

GroupId StudentName Week 1 Week 2
------------------------------------------
3840 Barry 100 95
3840 Jenny 100 99
3840 Kelly 98 87

• gacanepa: Thank you SO much for this tip! It is exactly what I was looking for.