SQL Server中key/value两列表如何转换为指定格式的JSON

解决方案 #

你可以使用JSON_QUERY结合FOR JSON PATH实现目标输出，完整查询语句如下：

declare @myTable table ([Key] nvarchar(max), [value] nvarchar(max)) 
insert into @myTable ([key],value) values ('Timestamp', convert(varchar(max), getdate(), 120))
insert into @myTable ([key],value) values ('Number', '123456')

-- 目标查询语句
SELECT 
    JSON_QUERY(CONCAT('{"', STRING_ESCAPE([key], 'json'), '":"', STRING_ESCAPE([value], 'json'), '"}'))
FROM @myTable
FOR JSON PATH

输出效果

执行后将直接得到你需要的JSON格式：

[{"Timestamp":"2024-05-20 14:30:00"},{"Number":"123456"}]

原理解释 #

• 你之前使用FOR JSON AUTO会直接将表的列名key、value作为JSON的键名，所以输出不符合预期
• STRING_ESCAPE函数会对键、值中的引号、斜杠等特殊字符做转义处理，避免生成非法JSON
• JSON_QUERY会将拼接好的单键值对字符串标记为合法JSON片段，避免外层FOR JSON PATH对其做二次转义
• 外层FOR JSON PATH会自动将所有行的JSON片段拼接为数组结构

内容的提问来源于stack exchange，提问作者Florian