SQL Server导入JSON后如何将Cost Center信息展开为行？

在SQL Server中尝试将指定JSON数据转换为表格形式时，无法将Cost Center（成本中心）的信息展开为单独行，当前查询结果中Cost Center的name与percent字段均为NULL，需要实现每个Cost Center信息单独成行的效果。

原SQL脚本：

DECLARE @JSONData AS NVARCHAR(4000) = N'{"employeeID": "1001","firstName": "John","lastName": "Doe","contract": [{"number": "1","startDate": "2024-01-01","endDate": "2024-12-31","costcenter": [{"name": "Sales","percent": "40"},{"name": "Support","percent": "60"}]},{"number": "2","startDate": "2025-01-01","costcenter": [{"name": "Production","percent": "30"},{"name": "Development","percent": "70"}]}]}'

SELECT 
    E.employeeID, E.firstName, E.lastName, 
    C.number AS contractnumber, C.startDate, C.endDate, 
    C.name, C.[percent], E.contract
FROM
    OPENJSON(@JSONData) WITH (
    employeeID VARCHAR(50)
    , firstName VARCHAR(50)
    , lastName VARCHAR(50)
    , contract NVARCHAR(max) AS JSON
) AS E
CROSS APPLY OPENJSON (E.contract)
WITH
(
    [number] VARCHAR(50),
    [startDate] VARCHAR(50),
    [endDate] VARCHAR(50),
    [name] VARCHAR(50) '$.costcenter.name',
    [percent] VARCHAR(50) '$.costcenter.percent'
) C

需要嵌套一层CROSS APPLY OPENJSON来解析costcenter数组——因为costcenter是嵌套在contract内的数组，直接在contract的WITH子句中取值无法正确解析数组元素。

修改后的SQL脚本：

DECLARE @JSONData AS NVARCHAR(4000) = N'{"employeeID": "1001","firstName": "John","lastName": "Doe","contract": [{"number": "1","startDate": "2024-01-01","endDate": "2024-12-31","costcenter": [{"name": "Sales","percent": "40"},{"name": "Support","percent": "60"}]},{"number": "2","startDate": "2025-01-01","costcenter": [{"name": "Production","percent": "30"},{"name": "Development","percent": "70"}]}]}'

SELECT 
    E.employeeID, 
    E.firstName, 
    E.lastName, 
    C.number AS contractnumber, 
    C.startDate, 
    C.endDate, 
    CC.name, 
    CC.[percent]
FROM
    OPENJSON(@JSONData) WITH (
        employeeID VARCHAR(50),
        firstName VARCHAR(50),
        lastName VARCHAR(50),
        contract NVARCHAR(max) AS JSON
    ) AS E
CROSS APPLY OPENJSON (E.contract) WITH (
    [number] VARCHAR(50),
    [startDate] VARCHAR(50),
    [endDate] VARCHAR(50),
    costcenter NVARCHAR(max) AS JSON -- 将costcenter标记为JSON类型，用于后续解析
) C
CROSS APPLY OPENJSON (C.costcenter) WITH (
    name VARCHAR(50),
    [percent] VARCHAR(50)
) CC -- 解析每个costcenter数组元素，生成单独行

• 第一层OPENJSON：解析员工基础信息，将contract字段标记为JSON类型，以便后续处理合同数组。
• 第二层OPENJSON：解析每个合同的基础信息，同时将costcenter字段标记为JSON类型，用于解析成本中心数组。
• 第三层OPENJSON：解析每个合同下的成本中心数组，将每个成本中心的name和percent拆分为单独行，实现需求效果。

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