SQL Server 2008兼容的JSON列提取与拆分优化方案问询

我有存储员工数据的视图vwSupervisors，其中ActiveDepartments列存储JSON格式的多部门信息。需要编写SQL Server 2008兼容的查询，将每个员工按所属部门拆分为单独行，同时提取DepartmentId和DepartmentName作为列。

当前使用嵌套子查询结合CHARINDEX、SUBSTRING的实现方式过于繁琐，希望获得更简洁的替代方案，且不能使用SQL Server 2008不支持的JSON_VALUE、OPENJSON等函数。

员工数据示例 #

Usage,EmployeeID,EmailAddress,Name,LastName,FirstName,MiddleName,GlobalID,Birthdate,UsageTags,NameAliases,ActiveDepartments,ADPriority
SupervisorOfStudents,10039,DLname@schoolname.edu,Dr. Fname M. Lname,Lname,Fname,M,NULL,NULL,"[{""seqNo"":1,""Tag"":""Employee:Active""},{""seqNo"":2,""Tag"":""Employee:ActiveChair""},{""seqNo"":3,""Tag"":""Employee:ActiveFaculty""},{""seqNo"":4,""Tag"":""Employee:InActiveStaff""}]",[{""seqNo"":1,""Name"":""Lname,Fname M""}],"[{""DepartmentType"":""Employee"",""Departments"":[{""seqNo"":1,""DepartmentId"":""99901"",""DepartmentName"":""Chemistry""},{""seqNo"":2,""DepartmentId"":""99902"",""DepartmentName"":""School of Science""},{""seqNo"":3,""DepartmentId"":""99903"",""DepartmentName"":""Continuing Education""}]}]",1
SupervisorOfStudents,10079,FLname@schoolname.edu,Ms Fname M. Lname,Lname,Fname,M,NULL,NULL,"[{""seqNo"":1,""Tag"":""Employee:Active""},{""seqNo"":2,""Tag"":""Employee:ActiveManager""},{""seqNo"":3,""Tag"":""Employee:ActiveStaff""},{""seqNo"":4,""Tag"":""Student:Attended""}]",[{""seqNo"":1,""Name"":""Lname,Fname M""},{""seqNo"":2,""Name"":""Lname,Fname Middle""},{""seqNo"":3,""Name"":""Lname-laLastName,Lname Fname""}],"[{""DepartmentType"":""Employee"",""Departments"":[{""seqNo"":1,""DepartmentId"":""99801"",""DepartmentName"":""Athletics""}]}]",1

当前实现代码 #

SELECT
    Usage,
    EmployeeID,
    EmailAddress,
    Name,
    LastName,
    FirstName,
    MiddleName,
    GlobalID,
    Birthdate,
    UsageTags,
    NameAliases,
    DepartmentName,
    DepartmentId
FROM
    (
        SELECT
            Usage,
            EmployeeID,
            EmailAddress,
            Name,
            LastName,
            FirstName,
            MiddleName,
            GlobalID,
            Birthdate,
            UsageTags,
            NameAliases,
            CASE 
                WHEN CHARINDEX('"DepartmentName":"', ActiveDepartments) > 0 THEN
                    SUBSTRING(
                        ActiveDepartments, 
                        CHARINDEX('"DepartmentName":"', ActiveDepartments) + LEN('"DepartmentName":"'), 
                        CHARINDEX('"', ActiveDepartments, CHARINDEX('"DepartmentName":"', ActiveDepartments) + LEN('"DepartmentName":"')) - CHARINDEX('"DepartmentName":"', ActiveDepartments) - LEN('"DepartmentName":"')
                    )
                ELSE NULL
            END AS DepartmentName,
            CASE 
                WHEN CHARINDEX('"DepartmentId":"', ActiveDepartments) > 0 THEN
                    SUBSTRING(
                        ActiveDepartments, 
                        CHARINDEX('"DepartmentId":"', ActiveDepartments) + LEN('"DepartmentId":"'), 
                        CHARINDEX('"', ActiveDepartments, CHARINDEX('"DepartmentId":"', ActiveDepartments) + LEN('"DepartmentId":"')) - CHARINDEX('"DepartmentId":"', ActiveDepartments) - LEN('"DepartmentId":"')
                    )
                ELSE NULL
            END AS DepartmentId
        FROM
            vwSupervisors
    ) AS DepartmentInfo
WHERE
    Usage = 'SupervisorBasicInfo';

针对SQL Server 2008的限制，可以借助数字辅助表结合字符串处理简化拆分逻辑，避免重复嵌套CHARINDEX的繁琐写法。

步骤1：创建数字辅助表 #

如果没有现成的数字表，可临时生成一个（这里生成1到100的数字，覆盖大部分部门数量场景）：

WITH Numbers AS (
    SELECT TOP 100 ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS N
    FROM sys.all_columns
)
SELECT * FROM Numbers;

步骤2：简化拆分查询 #

利用数字表逐个定位每个部门的位置，提取对应的DepartmentId和DepartmentName：

WITH Numbers AS (
    SELECT TOP 100 ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS N
    FROM sys.all_columns
)
SELECT
    s.Usage,
    s.EmployeeID,
    s.EmailAddress,
    s.Name,
    s.LastName,
    s.FirstName,
    s.MiddleName,
    s.GlobalID,
    s.Birthdate,
    s.UsageTags,
    s.NameAliases,
    -- 提取DepartmentName
    SUBSTRING(
        s.ActiveDepartments,
        CHARINDEX('"DepartmentName":"', s.ActiveDepartments, n.N) + LEN('"DepartmentName":"'),
        CHARINDEX('"', s.ActiveDepartments, CHARINDEX('"DepartmentName":"', s.ActiveDepartments, n.N) + LEN('"DepartmentName":"')) 
        - (CHARINDEX('"DepartmentName":"', s.ActiveDepartments, n.N) + LEN('"DepartmentName":"'))
    ) AS DepartmentName,
    -- 提取DepartmentId
    SUBSTRING(
        s.ActiveDepartments,
        CHARINDEX('"DepartmentId":"', s.ActiveDepartments, n.N) + LEN('"DepartmentId":"'),
        CHARINDEX('"', s.ActiveDepartments, CHARINDEX('"DepartmentId":"', s.ActiveDepartments, n.N) + LEN('"DepartmentId":"')) 
        - (CHARINDEX('"DepartmentId":"', s.ActiveDepartments, n.N) + LEN('"DepartmentId":"'))
    ) AS DepartmentId
FROM vwSupervisors s
CROSS JOIN Numbers n
WHERE 
    s.Usage = 'SupervisorBasicInfo'
    -- 确保当前数字对应的部门存在
    AND CHARINDEX('"DepartmentId":"', s.ActiveDepartments, n.N) > 0
    -- 避免重复提取同一部门
    AND (n.N = 1 OR CHARINDEX('"DepartmentId":"', s.ActiveDepartments, n.N) > CHARINDEX('"DepartmentId":"', s.ActiveDepartments, n.N - 1));

方案优势 #

• 消除嵌套子查询的冗余结构，逻辑更清晰
• 利用数字表的循环特性，自动将多部门拆分为多行
• 完全兼容SQL Server 2008，无需任何JSON专属函数

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