如何在SQL Server中将多行数据转换为键值对JSON对象

需求说明 #

将包含id、FkId、name、value字段的多行数据，按FkId分组后，转换为单一行的键值对JSON对象（示例：{"Name1":"Value1","Name2":"Value2"}）。

问题背景 #

使用JSON_ARRAY、FOR JSON PATH等原生JSON函数无法直接得到预期的键值对结构；手动通过STRING_AGG拼接JSON字符串时，会因特殊字符导致转义错误，生成的JSON格式不合法。

解决方案 #

通过JSON_QUERY结合STRING_ESCAPE、STRING_AGG的组合可以完美解决该问题，具体实现如下：

完整SQL代码 #

SELECT 
    FkId,
    JSON_QUERY('{' + STRING_AGG('"' + STRING_ESCAPE(name, 'json') + '":"' + STRING_ESCAPE(value, 'json') + '"', ',') + '}') AS KeyValueJson
FROM YourTable
GROUP BY FkId

函数作用解析 #

• STRING_ESCAPE(字段名, 'json')：对name和value中的JSON特殊字符（如双引号"、反斜杠\等）进行转义处理，确保生成的键值对符合JSON语法规范。
• STRING_AGG(拼接内容, 分隔符)：按FkId分组，将每个转义后的键值对字符串用逗号分隔拼接，形成"Name1":"Value1","Name2":"Value2"的中间结构。
• JSON_QUERY(内容)：将拼接后的中间结构包裹在{}中，通过JSON_QUERY标记该内容为合法JSON对象，避免SQL Server自动添加不必要的转义字符。

测试示例 #

假设表YourTable有如下数据：

执行上述SQL后，得到的结果如下：

可以看到，特殊字符已被正确转义，生成的JSON格式完全合法。

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