SQL展平未知数量嵌套JSON数组，实现单行列化关联父数组

现有JSON结构 #

[
    {
        "Workers": [
            {
                "location": "SAP Depot",
                "firstName": "Michael",
                "lastName": "Normanton",
                "sex": "Male",
                "fileOpened": "2018-02-02",
                "risksToBeAwareOf": "I can get dizzy",
                "personID": "3c1ba9ea",
                "documents": [
                    {
                        "docID": "26a41ac0",
                        "name": "MN Contract",
                        "date": "2022-08-15",
                        "Category": "HR"
                    },
                    {
                        "docID": "b5b158c9",
                        "name": "MN RA",
                        "date": "2021-12-17",
                        "Category": "Misc"
                    },
                    {
                        "docID": "b24lk3d8",
                        "name": "MN RA2",
                        "date": "2022-01-17",
                        "Category": "Misc"
                    }
                ]
            }
        ]
    }
]

说明：Workers数组包含多个员工对象，每个员工对象可能包含数量未知（或零）的documents嵌套数组。

当前使用的SQL导入语句 #

SELECT j2.*, j3.* INTO [Workers]
FROM OPENJSON(@JSON) 
WITH
(   
    Workers nvarchar(max) '$.Workers' as JSON
) j1
CROSS APPLY OPENJSON(J1.serviceUsers) -- 注：此处存在笔误，正确应为J1.Workers
WITH
(       
    FirstName nvarchar(50) '$.firstName',
    LastName nvarchar(75) '$.lastName',
    FileOpened nvarchar(10) '$.fileOpened',
    PersonID nvarchar(100) '$.personID',
    Documents nvarchar(max) '$.documents' as JSON
) j2
OUTER APPLY OPENJSON(J2.Documents)
WITH
(
    dDocID nvarchar(100) '$.docID',
    dDate nvarchar(30) '$.date',
    dName nvarchar(100) '$.name',
    dCategory nvarchar(500) '$.category'
) j3

当前查询结果 #

期望输出格式 #

要求将每个员工的所有文档信息横向展平为单行多列，示例如下：

解决方案：动态PIVOT实现横向展平 #

由于每个员工的文档数量不固定，需通过动态SQL结合PIVOT实现动态列生成：

DECLARE @cols NVARCHAR(MAX), @query NVARCHAR(MAX);

-- 提取所有文档字段的动态列名
WITH WorkerDocs AS (
    SELECT 
        j2.PersonID,
        'dDocID[' + CAST(ROW_NUMBER() OVER(PARTITION BY j2.PersonID ORDER BY j3.dDate) AS NVARCHAR) + ']' AS ColName,
        j3.dDocID AS ColValue,
        ROW_NUMBER() OVER(PARTITION BY j2.PersonID ORDER BY j3.dDate) AS DocNum
    FROM OPENJSON(@JSON) 
    WITH (Workers NVARCHAR(MAX) '$.Workers' AS JSON) j1
    CROSS APPLY OPENJSON(j1.Workers)
    WITH (
        FirstName NVARCHAR(50) '$.firstName',
        LastName NVARCHAR(75) '$.lastName',
        FileOpened NVARCHAR(10) '$.fileOpened',
        PersonID NVARCHAR(100) '$.personID',
        Documents NVARCHAR(MAX) '$.documents' AS JSON
    ) j2
    OUTER APPLY OPENJSON(j2.Documents)
    WITH (
        dDocID NVARCHAR(100) '$.docID',
        dDate NVARCHAR(30) '$.date',
        dName NVARCHAR(100) '$.name',
        dCategory NVARCHAR(500) '$.category'
    ) j3
    UNION ALL
    SELECT 
        j2.PersonID,
        'dDate[' + CAST(ROW_NUMBER() OVER(PARTITION BY j2.PersonID ORDER BY j3.dDate) AS NVARCHAR) + ']' AS ColName,
        j3.dDate AS ColValue,
        ROW_NUMBER() OVER(PARTITION BY j2.PersonID ORDER BY j3.dDate) AS DocNum
    FROM OPENJSON(@JSON) 
    WITH (Workers NVARCHAR(MAX) '$.Workers' AS JSON) j1
    CROSS APPLY OPENJSON(j1.Workers)
    WITH (
        FirstName NVARCHAR(50) '$.firstName',
        LastName NVARCHAR(75) '$.lastName',
        FileOpened NVARCHAR(10) '$.fileOpened',
        PersonID NVARCHAR(100) '$.personID',
        Documents NVARCHAR(MAX) '$.documents' AS JSON
    ) j2
    OUTER APPLY OPENJSON(j2.Documents)
    WITH (
        dDocID NVARCHAR(100) '$.docID',
        dDate NVARCHAR(30) '$.date',
        dName NVARCHAR(100) '$.name',
        dCategory NVARCHAR(500) '$.category'
    ) j3
    UNION ALL
    SELECT 
        j2.PersonID,
        'dName[' + CAST(ROW_NUMBER() OVER(PARTITION BY j2.PersonID ORDER BY j3.dDate) AS NVARCHAR) + ']' AS ColName,
        j3.dName AS ColValue,
        ROW_NUMBER() OVER(PARTITION BY j2.PersonID ORDER BY j3.dDate) AS DocNum
    FROM OPENJSON(@JSON) 
    WITH (Workers NVARCHAR(MAX) '$.Workers' AS JSON) j1
    CROSS APPLY OPENJSON(j1.Workers)
    WITH (
        FirstName NVARCHAR(50) '$.firstName',
        LastName NVARCHAR(75) '$.lastName',
        FileOpened NVARCHAR(10) '$.fileOpened',
        PersonID NVARCHAR(100) '$.personID',
        Documents NVARCHAR(MAX) '$.documents' AS JSON
    ) j2
    OUTER APPLY OPENJSON(j2.Documents)
    WITH (
        dDocID NVARCHAR(100) '$.docID',
        dDate NVARCHAR(30) '$.date',
        dName NVARCHAR(100) '$.name',
        dCategory NVARCHAR(500) '$.category'
    ) j3
    UNION ALL
    SELECT 
        j2.PersonID,
        'dCategory[' + CAST(ROW_NUMBER() OVER(PARTITION BY j2.PersonID ORDER BY j3.dDate) AS NVARCHAR) + ']' AS ColName,
        j3.dCategory AS ColValue,
        ROW_NUMBER() OVER(PARTITION BY j2.PersonID ORDER BY j3.dDate) AS DocNum
    FROM OPENJSON(@JSON) 
    WITH (Workers NVARCHAR(MAX) '$.Workers' AS JSON) j1
    CROSS APPLY OPENJSON(j1.Workers)
    WITH (
        FirstName NVARCHAR(50) '$.firstName',
        LastName NVARCHAR(75) '$.lastName',
        FileOpened NVARCHAR(10) '$.fileOpened',
        PersonID NVARCHAR(100) '$.personID',
        Documents NVARCHAR(MAX) '$.documents' AS JSON
    ) j2
    OUTER APPLY OPENJSON(j2.Documents)
    WITH (
        dDocID NVARCHAR(100) '$.docID',
        dDate NVARCHAR(30) '$.date',
        dName NVARCHAR(100) '$.name',
        dCategory NVARCHAR(500) '$.category'
    ) j3
)
SELECT @cols = STRING_AGG(QUOTENAME(ColName), ', ') WITHIN GROUP (ORDER BY DocNum, CHARINDEX(ColName, 'dDocID,dDate,dName,dCategory'))
FROM WorkerDocs
GROUP BY ColName, DocNum;

-- 构建并执行动态查询
SET @query = N'
WITH WorkerBase AS (
    SELECT 
        FirstName,
        LastName,
        FileOpened,
        PersonID,
        ''dDocID['' + CAST(ROW_NUMBER() OVER(PARTITION BY PersonID ORDER BY j3.dDate) AS NVARCHAR) + '']'' AS DocIDCol,
        j3.dDocID AS DocIDValue,
        ''dDate['' + CAST(ROW_NUMBER() OVER(PARTITION BY PersonID ORDER BY j3.dDate) AS NVARCHAR) + '']'' AS DateCol,
        j3.dDate AS DateValue,
        ''dName['' + CAST(ROW_NUMBER() OVER(PARTITION BY PersonID ORDER BY j3.dDate) AS NVARCHAR) + '']'' AS NameCol,
        j3.dName AS NameValue,
        ''dCategory['' + CAST(ROW_NUMBER() OVER(PARTITION BY PersonID ORDER BY j3.dDate) AS NVARCHAR) + '']'' AS CategoryCol,
        j3.dCategory AS CategoryValue
    FROM OPENJSON(@JSON) 
    WITH (Workers NVARCHAR(MAX) ''$.Workers'' AS JSON) j1
    CROSS APPLY OPENJSON(j1.Workers)
    WITH (
        FirstName NVARCHAR(50) ''$.firstName'',
        LastName NVARCHAR(75) ''$.lastName'',
        FileOpened NVARCHAR(10) ''$.fileOpened'',
        PersonID NVARCHAR(100) ''$.personID'',
        Documents NVARCHAR(MAX) ''$.documents'' AS JSON
    ) j2
    OUTER APPLY OPENJSON(j2.Documents)
    WITH (
        dDocID NVARCHAR(100) ''$.docID'',
        dDate NVARCHAR(30) ''$.date'',
        dName NVARCHAR(100) ''$.name'',
        dCategory NVARCHAR(500) ''$.category''
    ) j3
),
WorkerUnpivoted AS (
    SELECT 
        FirstName,
        LastName,
        FileOpened,
        PersonID,
        FinalColName,
        ColValue
    FROM WorkerBase
    UNPIVOT (
        ColValue FOR ColName IN (DocIDValue, DateValue, NameValue, CategoryValue)
    ) up
    CROSS APPLY (
        SELECT CASE ColName 
            WHEN ''DocIDValue'' THEN DocIDCol
            WHEN ''DateValue'' THEN DateCol
            WHEN ''NameValue'' THEN NameCol
            WHEN ''CategoryValue'' THEN CategoryCol
        END AS FinalColName
    ) cn
)
SELECT 
    FirstName,
    LastName,
    FileOpened,
    PersonID,
    ' + @cols + '
FROM WorkerUnpivoted
PIVOT (
    MAX(ColValue) FOR FinalColName IN (' + @cols + ')
) p
GROUP BY FirstName, LastName, FileOpened, PersonID;
';

EXEC sp_executesql @query, N'@JSON NVARCHAR(MAX)', @JSON = @JSON;

说明：

1. 通过ROW_NUMBER()为每个员工的文档编号，动态生成带序号的列名
2. 使用STRING_AGG()拼接动态列，结合PIVOT完成横向展平
3. 自动适配文档数量的变化，无需手动指定列数

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