Parsing JSON data from SQL Server table column

Question

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.

Please don't post your data as an image; it isn't helpful the the volunteers you're asking help from. Post your data as formatted text, or as DDL and DML statement, thanks — Thom A
– Thom A ♦, Commented Mar 11, 2019 at 20:59

Zhorov · Accepted Answer · 2019-03-12 09:52:13Z

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

Collectives™ on Stack Overflow

Parsing JSON data from SQL Server table column

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related