将含空格列名的SQL Server表转换为指定双层嵌套JSON的方法咨询

需求说明 #

将SQL Server表数据转换为双层嵌套JSON格式：外层为记录数组，每条记录对应内层数组，数组元素为{"key":"列名","value":"列值"}结构的对象，支持包含空格的列名。

源表示例 #

目标JSON格式 #

[
    [
        {"key":"ID","value":"123"},
        {"key":"Name","value":"abc"},
        {"key":"Program","value":"qew"},
        {"key":"Type","value":"tyu"},
        {"key":"Section","value":"dd"},
        {"key":"Director","value":"ghghjg"},
        {"key":"Project","value":"hkhjk"},
        {"key":"Sr Manager","value":"fghfgf"},
        {"key":"PCM","value":"gnhghj"},
        {"key":"Contractor","value":"gghgh"},
        {"key":"Cost Client","value":"gghhg"}
    ],
    [
        {"key":"ID","value":"456"},
        {"key":"Name","value":"yui"},
        {"key":"Program","value":"gdffgf"},
        {"key":"Type","value":"ghgf"},
        {"key":"Section","value":"jkjlkll"},
        {"key":"Director","value":"uiop"},
        {"key":"Project","value":"rtyuui"},
        {"key":"Sr Manager","value":"rfv"},
        {"key":"PCM","value":"ujmk"},
        {"key":"Contractor","value":"rfvtg"},
        {"key":"Cost Client","value":"efgg"}
    ]
]

修复后存储过程代码 #

该方案完全规避了FOR XML路径对含空格列名的限制，自动处理JSON特殊字符转义，兼容任意表结构：

CREATE PROCEDURE [dbo].[GetSQLtoJSON] @TableName VARCHAR(255)
AS
BEGIN
    SET NOCOUNT ON;
    -- 校验表是否存在
    IF OBJECT_ID(@TableName) IS NULL
    BEGIN
        SELECT Json = '';
        RETURN
    END;

    DECLARE @ColUnion NVARCHAR(MAX), @SQL NVARCHAR(MAX);

    -- 拼接所有列的key-value查询逻辑
    SELECT @ColUnion = STRING_AGG(
        'SELECT ''' + STRING_ESCAPE(name, 'json') + ''' AS [key], CAST(' + QUOTENAME(name) + ' AS NVARCHAR(MAX)) AS [value]',
        ' UNION ALL '
    ) WITHIN GROUP (ORDER BY column_id)
    FROM sys.columns
    WHERE [object_id] = OBJECT_ID(@TableName);

    -- 生成最终动态SQL
    SET @SQL = N'
        SELECT Json = (
            SELECT (
                SELECT ' + @ColUnion + N'
                FOR JSON PATH
            )
            FROM ' + QUOTENAME(@TableName) + N'
            FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
        )
    ';

    EXECUTE SP_EXECUTESQL @SQL;
END
GO

注：该存储过程依赖SQL Server 2017及以上版本提供的STRING_AGG函数，若需兼容更低版本，可替换为FOR XML PATH方式拼接列查询逻辑。

测试验证代码 #

-- 创建测试表
CREATE TABLE [dbo].[Test1](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [Col1] [int] NOT NULL,
    [Col 2] varchar(50)
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[Test1] ON 
GO
INSERT [dbo].[Test1] ([ID], [Col1], [Col 2]) VALUES (1, 0,'ABCD')
GO
INSERT [dbo].[Test1] ([ID], [Col1] ,[Col 2]) VALUES (2, 1, 'POIU')
GO
SET IDENTITY_INSERT [dbo].[Test1] OFF
GO

-- 调用存储过程测试
EXEC [dbo].[GetSQLtoJSON] 'Test1'

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