从 SQL Server 2016+ 创建 JSON(用于 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，因为它具有处理 JSON 格式并处理嵌套的 FOR 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"的值成为数据库表中的字段，或者为每个当前标题项(数据值、排名、波段等(创建一个新表。

我以为我可以从 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"
}

这有帮助吗？