Создать JSON из SQL Server 2016+ (используется для команды пути JSON)

Question

Я пытаюсь создать вывод в определенном формате JSON, вот пример вывода (будет повторяться много раз - один раз для каждого университета):

образец изображения JSON

{
    "id": "37e556ae6b9f620d2b7262d3de971c40",
    "recordType": "school",
    "created": 1532531378,
    "updated": 1532531378,
    "published": 1532531378,
    "name": "ABC University – ABC School of Management",
    "schoolName": "ABC School of Management",
    "universityName": "ABC University",
    "sortableName": "ABC School of Management"
}

Я думал, что буду использовать SQL для этого, так как он имеет команду FOR JSON, которая обрабатывает форматирование JSON и будет иметь дело с вложением. Я могу воссоздать вышесказанное, используя следующий код, когда я жестко кодирую значения:

SELECT 
     HASHBYTES('md5','something') AS id
     ,'school' AS recordType
     ,getdate() AS created
     ,getdate() AS updated
     ,getdate() AS published
     ,'ABC University – ABC School of Management' AS name
     ,'ABC School of Management' as schoolname 
     ,'ABC University' as universityName
     , 'ABC School of Management' as sortableName
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER 

Вопрос, который у меня возник, заключается в том, что мне нужно создать этот вывод 100 раз для разных университетов. Я хочу создать таблицы базы данных в SQL и запустить что-то поверх него. Вот пример данных в Excel, я отфильтровал список, чтобы показать только данные, необходимые для вышеупомянутого и пару других точек данных, которые необходимы в более поздних отдельных выходных данных (я буду беспокоиться о других выходных данных потом):

IndiUID SchoolCode  DataValue   Ranking Band    RegionalRank
OVERALL_SCORE   XYZ     105 D   23
SCHOOL_NAME_FORMAL  XYZ XYZ Institute of Management         
SCHOOL_NAME_SORTABLE    XYZ XYZ Institute of Management         
SCHOOL_NAME_AND_BUSINESS_SCHOOL_NAME    XYZ XYZ Institute of Management, XYZ School of Business         
STUDENTS_MEDIAN_AGE XYZ 26          
OVERALL_SCORE   ABC     35  B   3
SCHOOL_NAME_FORMAL  ABC ABC School of Management            
SCHOOL_NAME_SORTABLE    ABC ABC School of Management            
SCHOOL_NAME_AND_BUSINESS_SCHOOL_NAME    ABC ABC University – ABC School of Management           
STUDENTS_MEDIAN_AGE ABC 26          

изображение образца данных

Я просто не могу понять, как мне нужно структурировать свои таблицы, чтобы я мог выделять соответствующие части с помощью SQL. Мне интересно, следует ли мне поворачивать таблицу так, чтобы значения IndiUID становились полями в таблице базы данных или создавали новую таблицу для каждого из текущих элементов заголовка (DataValue, Ranking, Band и т. Д.).

Я думал, что могу выбрать из таблицы, где indiUID = OVERALL_SCORE, SCHOOL_NAME_FORMAL и т. Д., Но мне интересно, если это слишком грязно.

Вот где я застрял - как мне организовать эти данные в таблицы, чтобы иметь возможность извлечь их? В основном речь идет о создании вывода JSON, поэтому не имеет значения, что часть БД является особенно элегантным решением (это не будет - я очень прост в SQL!).

Answer 1

Вы можете попробовать следующее

Пример данных:

CREATE TABLE [#school]
([school_name_and_business_school_name] VARCHAR(255), 
 [school_name]                          VARCHAR(255), 
 [university_name]                      VARCHAR(255), 
 [school_sortable]                      VARCHAR(255)
);

INSERT INTO [#school]
VALUES
('ABC University – ABC School of Management', 
 'ABC School of Management', 
 'ABC University', 
 'ABC School of Management'
),
('XYZ University – XYZ School of Management', 
 'XYZ School of Management', 
 'XYZ University', 
 'XYZ School of Management'
),
('QWE University – QWEC School of Management', 
 'QWE School of Management', 
 'QWE University', 
 'QWE School of Management'
);

И затем используйте этот запрос:

SELECT HASHBYTES('md5', 'something') AS [id], 
  'school' AS [recordType], 
  GETDATE() AS [created], 
  GETDATE() AS [updated], 
  GETDATE() AS [published], 
  [school_name_and_business_school_name] AS [name], 
  [school_name] AS [schoolName], 
  [university_name] AS [universityName], 
  [school_sortable] AS [sortableName]
FROM   [#school] 
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER;

Дает вам следующий результат:

{"id":"Q3uTDbhLgHnC3YBKcZNrXw==",
"recordType":"school",
"created":"2018-09-07T10:14:44.130",
"updated":"2018-09-07T10:14:44.130",
"published":"2018-09-07T10:14:44.130",
"name":"ABC University – ABC School of Management",
"schoolName":"ABC School of Management",
"universityName":"ABC University",
"sortableName":"ABC School of Management"
},

{"id":"Q3uTDbhLgHnC3YBKcZNrXw==",
"recordType":"school",
"created":"2018-09-07T10:14:44.130",
"updated":"2018-09-07T10:14:44.130",
"published":"2018-09-07T10:14:44.130",
"name":"XYZ University – XYZ School of Management",
"schoolName":"XYZ School of Management",
"universityName":"XYZ University",
"sortableName":"XYZ School of Management"
},

{"id":"Q3uTDbhLgHnC3YBKcZNrXw==",
"recordType":"school",
"created":"2018-09-07T10:14:44.130",
"updated":"2018-09-07T10:14:44.130",
"published":"2018-09-07T10:14:44.130",
"name":"QWE University – QWEC School of Management",
"schoolName":"QWE School of Management",
"universityName":"QWE University",
"sortableName":"QWE School of Management"
}

Это помогает?