Блог

Главная - Вопросы по программированию - Анализ данных JSON из столбца таблицы SQL Server

Анализ данных JSON из столбца таблицы SQL Server

#json #sql-server #stored-procedures #sql-view

#json #sql-сервер #хранимые процедуры #sql-просмотр

Вопрос:

Я пытаюсь проанализировать данные JSON из таблицы в SQL Server 2017. У меня есть представление, которое возвращает эти данные: 

 | 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"}]}                                              |

Debrief Name Столбец может содержать несколько записей с одинаковым именем отчета и Version . Также существует несколько версий для каждого отчета. И для каждого имени отчета и комбинации версий существует множество Answer Questions и связанных Answer Options . Теперь столбец Answer Options содержит запись JSON, которую мне нужно проанализировать.

Итак, мой первоначальный запрос, который выглядит примерно так, как показано ниже: 

 SELECT * 
FROM [dbo].<MY VIEW> 
WHERE [Debrief Name] = 'Observer Report' AND Version = 11

который вернул бы приведенные ниже данные:

 | 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"}]} |

Теперь из этого возвращенного результата для каждого Answer Question мне нужно проанализировать связанные Answer Options данные JSON и извлечь поле значения для всех атрибутов отображения. Так, например, строка JSON в Answer Options для вопроса «Вы в резервировании?» выглядит следующим образом:

 "Options":[
    {
        "Display":"330",
        "Value":"330",
        "Selected":false
    },
    {
        "Display":"320",
        "Value":"320",
        "Selected":false
    },
    {
        "Display":"S80",
        "Value":"S80",
        "Selected":false
    }
]

Итак, мне нужно извлечь поля «Значения» и вернуть что-то вроде массива со значениями {330, 320, 195}.

В заключение я хочу создать запрос, в котором, когда я предоставляю имя отчета и номер версии, он возвращает мне Answer Question и все Answer Option значения.

Я подумываю об использовании хранимой процедуры, подобной приведенной ниже:

 CREATE PROCEDURE myProc 
    @DebriefName NVARCHAR(255), 
    @Version INT
AS
    SELECT * 
    FROM [dbo].[myView] 
    WHERE [Debrief Name] = @DebriefName 
      AND Version = @Version
GO;

И затем создайте другую хранимую процедуру, которая получит этот результат из myProc , а затем выполнит синтаксический анализ JSON:

 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;

Я не эксперт в SQL, поэтому не уверен, как это сделать. Я прочитал о разборе Json в SQL здесь и чувствую, что могу это использовать, но не уверен, как в моем контексте.

Комментарии:

1.Пожалуйста, не публикуйте свои данные в виде изображения; это не поможет добровольцам, у которых вы просите помощи. Опубликуйте свои данные в форматированном text виде или в виде инструкции DDL и DML, спасибо

Ответ №1:

Если вы хотите проанализировать данные JSON в Answer Options столбце и извлечь Value поле, вы можете попробовать использовать следующий подход, используя OPENJSON() и 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'

Вывод:

 Values
330, 320, 195

Если вы хотите создать свой оператор с использованием хранимой процедуры, используйте этот подход:

 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

Вывод:

 AnswerQuestion  Values
Crew Position:  CA, RC, FO
Domicile:       BOS, CLT