MSSQL JSON 选择列作为对象键

Question

我正在尝试直接从我的MSSQL DB中选择JSON结构。

我想要的是选择 ID 字段作为对象的键。

假设我有以下列：

ID | Firstname | LastName   | DateOfBirth | Gender
1  | Paul      | Van Tonder | 1990-03-11  | Male

我想选择JSON格式，例如：

[
{
"1": {
"FirstName": "Paul",
"LastName": "Van Tonder",
"DateOfBirth": "1990-03-11",
"Gender": "Male"
}
}
]

Answer 1

我不认为，您可以仅使用FOR JSON生成具有可变键名的 JSON 输出。因此，您需要使用基于字符串的方法：

• 仅使用字符串连接/聚合
• 结合使用FOR JSON和字符串聚合

桌子：

CREATE TABLE Data (
ID int, 
FirstName varchar(50), 
LastName varchar(50), 
DateOfBirth date, 
Gender varchar(50)   
)
INSERT INTO Data (ID, FirstName, LastName, DateOfBirth, Gender)
VALUES 
(1, 'Paul',  'Van Tonder', '1990-03-11', 'Male'),
(2, 'John',  'Van Tonder', '1990-03-12', 'Male')

语句(使用字符串聚合和串联(：

SELECT CONCAT(
'[',
STRING_AGG(
CONVERT(nvarchar(max), CONCAT(
'{"FirstName":"', 
FirstName, 
'", "LastName":"', 
LastName, 
'", "DateOfBirth":"', 
CONVERT(varchar, DateOfBirth), 
'", "Gender": "', 
Gender, 
'"}')
),
','
),
']'
)
FROM Data

语句(使用FOR JSON和字符串聚合(：

SELECT CONCAT(
'[',
STRING_AGG(CONCAT('{"', d.ID, '": ',  c.Json), ','),
']'
)
FROM Data d
CROSS APPLY (
SELECT FirstName, LastName, DateOfBirth, Gender 
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
) c (Json)

结果：

[
{"1":{"FirstName":"Paul","LastName":"Van Tonder","DateOfBirth":"1990-03-11","Gender":"Male"}},
{"2":{"FirstName":"John","LastName":"Van Tonder","DateOfBirth":"1990-03-12","Gender":"Male"}}
]