I am trying to parse JSON data from a table in SQL Server 2017. I have a view that returns this data:
| Debrief Name | Version | Answer Question | Answer Options |
+-------------------+-----------+--------------------------+--------------------------------------------------------------------------------------------------------------------------+
| Observer Report | 7 | Division: | {"Options":[{"Display":"Domestic","Value":"Domestic"},{"Display":"International","Value":"International"}]} |
| Observer Report | 7 | Are you on reserve? | {"Options":[{"Display":"Yes - Long Call Line","Value":"Yes"},{"Display":"No","Value":"No"}]} |
| Observer Report | 11 | Crew Position: | {"Options":[{"Display":"CA","Value":"CA"},{"Display":"RC","Value":"RC"},{"Display":"FO","Value":"FO"}]} |
| Observer Report | 11 | Domicile: | {"VisibleLines":2,"Options":[{"Display":"BOS","Value":"BOS"},{"Display":"CLT","Value":"CLT"}]} |
| Training Debrief | 12 | TRAINING CREW POSITION | {"VisibleLines":2,"Options":[{"Display":"CA","Value":"CA"},{"Display":"FO","Value":"FO"}]} |
| Training Debrief | 12 | AIRCRAFT | {"VisibleLines":2,"Options":[{"Display":"777","Value":"777"},{"Display":"767","Value":"767"}]} |
| Security Debrief | 9 | Aircraft Type | {"Options":[{"Display":"MD-80","Value":"MD-80"},{"Display":"777","Value":"777"},{"Display":"767/757","Value":"767/757"}]}|
| News Digest | 2 | Do you read Digest? | {"Options":[{"Display":"Yes","Value":"Yes"},{"Display":"No","Value":"No"}]} |
The Debrief Name column can have multiple records for same debrief name and Version. Also there are multiple versions for each debrief. And for each debrief name and version combination, there are set of Answer Questions and related Answer Options. Now the column Answer Options contain JSON record which I need to parse.
So my initial query that is something like below:
SELECT *
FROM [dbo].<MY VIEW>
WHERE [Debrief Name] = 'Observer Report' AND Version = 11
which would return below data:
| Debrief Name | Version | Answer Question | Answer Options |
+---------------------+--------------+-----------------------+-----------------------------------------------------------------------------------------------------------------+
| Observer Report | 11 | Crew Position: | {"Options":[{"Display":"CA","Value":"CA"},{"Display":"RC","Value":"RC"}]} |
| Observer Report | 11 | Domicile: | {"VisibleLines":2,"Options":[{"Display":"BOS","Value":"BOS"},{"Display":"CLT","Value":"CLT"}]} |
| Observer Report | 11 | Fleet: | {"Options":[{"Display":"330","Value":"330"},{"Display":"320","Value":"320"}]} |
| Observer Report | 11 | Division: | {"Options":[{"Display":"Domestic","Value":"Domestic"},{"Display":"International","Value":"International"}]} |
| Observer Report | 11 | Are you on reserve? | {"Options":[{"Display":"Yes - Long Call Line","Value":"Yes - Long Call Line"},{"Display":"No","Value":"No"}]} |
Now from this returned result, for each Answer Question I need to parse the related Answer Options JSON data and extract the Value field for all the display attribute. So for example the JSON string in Answer Options for question "Are you on reserver?" looks like this:
"Options":[
{
"Display":"330",
"Value":"330",
"Selected":false
},
{
"Display":"320",
"Value":"320",
"Selected":false
},
{
"Display":"S80",
"Value":"S80",
"Selected":false
}
]
So I need to extract "Value" fields and return something like an array with values {330, 320, 195}.
In conclusion I want to construct a query where when I provide the Debrief Name and VersionNumber, it returns me the Answer Question and all the Answer Option values.
I am thinking of using a stored procedure like below:
CREATE PROCEDURE myProc
@DebriefName NVARCHAR(255),
@Version INT
AS
SELECT *
FROM [dbo].[myView]
WHERE [Debrief Name] = @DebriefName
AND Version = @Version
GO;
And then have another stored procedure that will capture this result from myProc and then do the JSON parsing:
CREATE PROCEDURE parseJSON
@DebriefName NVARCHAR(255),
@Version INT
AS
EXEC myProc @DebriefName, @Version; //Need to capture the result data in a temp table or something
// Parse the JSON data for each question item in temp table
GO;
I am not an expert in SQL so not sure how to do this. I read about Json parsing in SQL here and feel like I can use that but not sure how to in my context.
If you want to parse JSON data in Answer Options column and extract the Value field, you may try with the following approach, using OPENJSON() and STRING_AGG():
DECLARE @json nvarchar(max)
SET @json = N'{
"Options": [
{
"Display": "330",
"Value": "330",
"Selected": false
},
{
"Display": "320",
"Value": "320",
"Selected": false
},
{
"Display": "195",
"Value": "195",
"Selected": false
}
]
}'
SELECT STRING_AGG(x.[value], ', ') AS [Values]
FROM OPENJSON(@json, '$.Options') j
CROSS APPLY (SELECT * FROM OPENJSON(j.[value])) x
WHERE x.[key] = 'Value'
Output:
Values
330, 320, 195
If you want to build your statement using stored procedure, use this approach:
CREATE TABLE myTable (
DebriefName nvarchar(100),
Version int,
AnswerQuestion nvarchar(1000),
AnswerOptions nvarchar(max)
)
INSERT INTO myTable
(DebriefName, Version, AnswerQuestion, AnswerOptions)
VALUES
(N'Observer Report', 7, N'Division:' , N'{"Options":[{"Display":"Domestic","Value":"Domestic"},{"Display":"International","Value":"International"}]}'),
(N'Observer Report', 7, N'Are you on reserve?' , N'{"Options":[{"Display":"Yes - Long Call Line","Value":"Yes"},{"Display":"No","Value":"No"}]}'),
(N'Observer Report', 11, N'Crew Position:' , N'{"Options":[{"Display":"CA","Value":"CA"},{"Display":"RC","Value":"RC"},{"Display":"FO","Value":"FO"}]}'),
(N'Observer Report', 11, N'Domicile:' , N'{"VisibleLines":2,"Options":[{"Display":"BOS","Value":"BOS"},{"Display":"CLT","Value":"CLT"}]}'),
(N'Training Debrief', 12, N'TRAINING CREW POSITION', N'{"VisibleLines":2,"Options":[{"Display":"CA","Value":"CA"},{"Display":"FO","Value":"FO"}]}'),
(N'Training Debrief', 12, N'AIRCRAFT' , N'{"VisibleLines":2,"Options":[{"Display":"777","Value":"777"},{"Display":"767","Value":"767"}]}'),
(N'Security Debrief', 9, N'Aircraft Type' , N'{"Options":[{"Display":"MD-80","Value":"MD-80"},{"Display":"777","Value":"777"},{"Display":"767/757","Value":"767/757"}]}'),
(N'News Digest', 2, N'Do you read Digest?' , N'{"Options":[{"Display":"Yes","Value":"Yes"},{"Display":"No","Value":"No"}]}')
SELECT
t.AnswerQuestion,
STRING_AGG(x.[value], ', ') AS [Values]
FROM myTable t
CROSS APPLY (SELECT * FROM OPENJSON(t.AnswerOptions, '$.Options')) j
CROSS APPLY (SELECT * FROM OPENJSON(j.[value])) x
WHERE
DebriefName = N'Observer Report' AND
t.Version = 11 AND
x.[key] = 'Value'
GROUP BY
t.DebriefName,
t.Version,
t.AnswerQuestion
Output:
AnswerQuestion Values
Crew Position: CA, RC, FO
Domicile: BOS, CLT