MS SQL中标量函数的替代方案：将SQL数据转换为JSON对象

好的，除了你当前使用的自定义函数结合FOR JSON PATH的方式，还有几种灵活的方法可以将SQL Server中的数据转换为JSON对象，适配不同的场景需求：

1. 直接使用FOR JSON AUTO自动生成嵌套结构 #

FOR JSON AUTO会根据你的查询表关系自动推断JSON的嵌套层级，不需要手动指定属性路径，写法更简洁，非常适合你的关联表场景：

SELECT 
    -- 保留你原有的字段处理逻辑
    CASE WHEN ISNULL(a.SchoolDistrictId,'')<>'' THEN SUBSTRING(a.SchoolDistrictId,1,CHARINDEX(':',a.SchoolDistrictId)-1)ELSE NULL END AS 'district.name',
    CASE WHEN ISNULL(a.SchoolDistrictId,'')<>'' THEN SUBSTRING(a.SchoolDistrictId,CHARINDEX(':',a.SchoolDistrictId)+1,LEN(a.SchoolDistrictId))ELSE NULL END AS 'district.id',
    CASE WHEN ISNULL(a.SchoolDistrictSEOPath,'')<>'' AND a.SchoolDistrictSEOPath<>'0' THEN a.SchoolDistrictSEOPath ELSE NULL END AS 'district.seo',
    CASE WHEN ISNULL(a.SchoolElementary,'')<>'' THEN CASE WHEN CHARINDEX(':',a.SchoolElementary)>0 THEN SUBSTRING(a.SchoolElementary,1,CHARINDEX(':',a.SchoolElementary)-1) ELSE a.SchoolElementary END ELSE NULL END AS 'elementary.name',
    CASE WHEN ISNULL(a.SchoolElementary,'')<>'' THEN CASE WHEN CHARINDEX(':',a.SchoolElementary)>0 THEN SUBSTRING(a.SchoolElementary,CHARINDEX(':',a.SchoolElementary)+1,LEN(a.SchoolElementary)) ELSE NULL END ELSE NULL END AS 'elementary.id',
    CASE WHEN ISNULL(a.SchoolHigh,'')<>'' THEN CASE WHEN CHARINDEX(':',a.SchoolHigh)>0 THEN SUBSTRING(a.SchoolHigh,1,CHARINDEX(':',a.SchoolHigh)-1)ELSE a.SchoolHigh END ELSE NULL END AS 'high.name',
    CASE WHEN ISNULL(a.SchoolHigh,'')<>'' THEN CASE WHEN CHARINDEX(':',a.SchoolHigh)>0 THEN SUBSTRING(a.SchoolHigh,CHARINDEX(':',a.SchoolHigh)+1,len(a.SchoolHigh))ELSE NULL END ELSE NULL END AS 'high.id',
    CASE WHEN ISNULL(a.SchoolMiddle,'')<>'' THEN CASE WHEN CHARINDEX(':',a.SchoolMiddle)>0 THEN SUBSTRING(a.SchoolMiddle,1,CHARINDEX(':',a.SchoolMiddle)-1)ELSE a.SchoolMiddle END ELSE NULL END AS 'middle.name',
    CASE WHEN ISNULL(a.SchoolMiddle,'')<>'' THEN CASE WHEN CHARINDEX(':',a.SchoolMiddle)>0 THEN SUBSTRING(a.SchoolMiddle,CHARINDEX(':',a.SchoolMiddle)+1,len(a.SchoolMiddle))ELSE NULL END ELSE NULL END AS 'middle.id'
FROM Table_Name1 a WITH (NOLOCK)
JOIN Table_Name2 b ON a.IdListing = b.IdListing
FOR JSON AUTO, WITHOUT_ARRAY_WRAPPER;

2. 用JSON_MODIFY动态构建JSON对象 #

如果需要更细粒度地控制JSON的生成过程（比如动态添加/修改属性），可以使用JSON_MODIFY逐步组装JSON：

DECLARE @resultJson NVARCHAR(MAX) = '{}';

SELECT 
    @resultJson = JSON_MODIFY(
        JSON_MODIFY(
            JSON_MODIFY(@resultJson, '$.district.name', CASE WHEN ISNULL(a.SchoolDistrictId,'')<>'' THEN SUBSTRING(a.SchoolDistrictId,1,CHARINDEX(':',a.SchoolDistrictId)-1)ELSE NULL END),
            '$.district.id', CASE WHEN ISNULL(a.SchoolDistrictId,'')<>'' THEN SUBSTRING(a.SchoolDistrictId,CHARINDEX(':',a.SchoolDistrictId)+1,LEN(a.SchoolDistrictId))ELSE NULL END
        ),
        '$.district.seo', CASE WHEN ISNULL(a.SchoolDistrictSEOPath,'')<>'' AND a.SchoolDistrictSEOPath<>'0' THEN a.SchoolDistrictSEOPath ELSE NULL END
        -- 依次添加其他elementary、high、middle相关属性
    )
FROM Table_Name1 a WITH (NOLOCK)
JOIN Table_Name2 b ON a.IdListing = b.IdListing
WHERE a.IdListing = @IdListing; -- 替换为具体ID或变量

SELECT @resultJson AS SchoolInfoJson;

3. 结合JSON_QUERY嵌入子查询JSON结果 #

如果你的数据来自多个关联表，还可以用JSON_QUERY将子查询生成的JSON作为嵌套属性嵌入主结果中，让结构更清晰：

SELECT
    JSON_QUERY((
        SELECT 
            SUBSTRING(a.SchoolDistrictId,1,CHARINDEX(':',a.SchoolDistrictId)-1) AS name,
            SUBSTRING(a.SchoolDistrictId,CHARINDEX(':',a.SchoolDistrictId)+1,LEN(a.SchoolDistrictId)) AS id,
            CASE WHEN ISNULL(a.SchoolDistrictSEOPath,'')<>'' AND a.SchoolDistrictSEOPath<>'0' THEN a.SchoolDistrictSEOPath ELSE NULL END AS seo
        FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
    )) AS district,
    -- 同理处理小学、中学、高中信息
    JSON_QUERY((
        SELECT 
            CASE WHEN CHARINDEX(':',a.SchoolElementary)>0 THEN SUBSTRING(a.SchoolElementary,1,CHARINDEX(':',a.SchoolElementary)-1) ELSE a.SchoolElementary END AS name,
            CASE WHEN CHARINDEX(':',a.SchoolElementary)>0 THEN SUBSTRING(a.SchoolElementary,CHARINDEX(':',a.SchoolElementary)+1,LEN(a.SchoolElementary)) ELSE NULL END AS id
        FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
    )) AS elementary
FROM Table_Name1 a WITH (NOLOCK)
JOIN Table_Name2 b ON a.IdListing = b.IdListing
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER;

4. CLR自定义函数（适合复杂逻辑场景） #

如果你的SQL Server启用了CLR集成，还可以编写C#或VB.NET的CLR函数来处理数据转换。这种方式适合需要特殊字符串处理、复杂业务逻辑的场景，但需要管理员权限启用CLR，一般仅在其他方法无法满足时使用。

选择建议 #

• 快速生成基于表结构的JSON：优先用FOR JSON AUTO
• 自定义JSON结构：继续用FOR JSON PATH（或你当前的函数方式）
• 动态构建或修改JSON：用JSON_MODIFY
• 复杂嵌套关联：结合JSON_QUERY

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