如何在SQL Server 2022中无需枚举所有列将指定主键行转为JSON数组

以下脚本无需逐一指定列名，即可将目标行转换为你需要的JSON数组格式：

完整脚本 #

DECLARE @TableName NVARCHAR(128) = N'mytable'; -- 替换为你的表名
DECLARE @PkValue INT = 123; -- 替换为目标主键值
DECLARE @PkColumnName NVARCHAR(128) = N'pkValue'; -- 替换为实际的主键列名
DECLARE @DynamicSQL NVARCHAR(MAX);

-- 动态生成查询语句
SELECT @DynamicSQL = N'
SELECT JSON_QUERY(''['' + STRING_AGG(JSON_OBJECT(
    ''TableName'', ''' + QUOTENAME(@TableName, '''') + ''',
    ''ColumnName'', ''' + QUOTENAME(c.COLUMN_NAME, '''') + ''',
    ''pkValue'', ' + QUOTENAME(@PkColumnName) + ',
    ''colValue'', ' + QUOTENAME(c.COLUMN_NAME) + ',
    ''colType'', ''' + QUOTENAME(c.DATA_TYPE, '''') + '''
), '','') + ']'') AS ResultJSON
FROM ' + QUOTENAME(@TableName) + '
WHERE ' + QUOTENAME(@PkColumnName) + ' = ' + CAST(@PkValue AS NVARCHAR(20));
FROM INFORMATION_SCHEMA.COLUMNS c
WHERE c.TABLE_NAME = @TableName;

-- 执行动态SQL
EXEC sp_executesql @DynamicSQL;

核心逻辑说明 #

1. 自动获取列元数据：通过INFORMATION_SCHEMA.COLUMNS读取目标表的所有列名和数据类型，无需手动枚举列
2. 构造单个列的JSON对象：用SQL Server 2022新增的JSON_OBJECT函数，直接生成每个列对应的结构化JSON对象
3. 拼接为JSON数组：通过STRING_AGG将所有列的JSON对象拼接为逗号分隔字符串，再包裹[]形成标准JSON数组
4. 确保JSON类型输出：用JSON_QUERY标记结果为JSON类型，避免返回纯字符串

特殊场景适配 #

• 如果主键值是字符串类型，需调整赋值和条件语句：

  DECLARE @PkValue NVARCHAR(50) = N'123';
  -- 动态SQL中的WHERE条件修改为：
  WHERE ' + QUOTENAME(@PkColumnName) + ' = ''' + @PkValue + ''''
  

• 日期、时间等特殊数据类型会被JSON_OBJECT自动转换为符合JSON规范的格式
• 含特殊字符的列名会被QUOTENAME(..., '''')自动转义，避免语法错误

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