SQL Server中如何按指定docid输出单个JSON字典？

SQL Server 2017+ 解法 #

直接通过字符串聚合与拼接实现，同时处理JSON特殊字符避免格式错误：

SELECT CONCAT('{', STRING_AGG(CONCAT('"', STRING_ESCAPE([key], 'json'), '":"', STRING_ESCAPE([value], 'json'), '"'), ','), '}') AS single_json_dict
FROM mytable
WHERE docid = 456;

核心步骤说明 #

• 转义特殊字符：使用STRING_ESCAPE([key], 'json')和STRING_ESCAPE([value], 'json')处理键/值中的引号、反斜杠等字符，确保生成的JSON符合语法规范。
• 拼接单行键值对：将每行的key和value转换为"key":"value"格式的字符串片段。
• 聚合所有片段：通过STRING_AGG函数将所有键值对片段用逗号连接成完整的键值对列表。
• 包裹为JSON对象：用CONCAT给聚合后的字符串添加首尾大括号{}，形成标准的JSON字典结构。

其他数据库适配方案 #

PostgreSQL #

替换SQL Server的STRING_AGG为原生string_agg，手动处理特殊字符转义：

SELECT CONCAT('{', string_agg(concat('"', replace("key", '"', '\\"'), '":"', replace("value", '"', '\\"'), '"'), ','), '}') AS single_json_dict
FROM mytable
WHERE docid = 456;

MySQL #

使用GROUP_CONCAT实现聚合，搭配JSON_QUOTE完成特殊字符转义：

SELECT CONCAT('{', GROUP_CONCAT(CONCAT(JSON_QUOTE(`key`), ':', JSON_QUOTE(`value`)) SEPARATOR ','), '}') AS single_json_dict
FROM mytable
WHERE docid = 456;

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