SQL查询输出层级化JSON的最优实现方案

我在尝试将SQL查询结果输出为符合要求的JSON格式时遇到了问题。原始查询仅涉及两张表，使用FOR JSON Auto只会返回扁平化的对象数组，实际数据存在层级关系：Q2CNum -> LineItems -> Identifier。
我尝试过使用FOR JSON Path，但它仅能对单个JSON对象做结构调整，无法将所有Identifier聚合到所属的父级LineItem下。
之后我通过新增自连接的方式调整查询，FOR JSON Path Auto可以输出更符合要求的结构，但查询耗时从2秒飙升到20秒，性能下降严重，我猜测是自连接写法不合理或者多表连接的效率过低导致的，我无法公开更多业务细节，希望能获得可行的优化方案。

更新1 #

当前基础查询执行后只能得到扁平化JSON（仅为对象数组），查询代码如下：

Select OWS.Q2CNum, 
       OWS.LineItem, 
       OWS.Identifier, 
       OWS.ProductType, 
       OWS.Workstation, 
       OWS.Calculation, 
       OWS.UnitOfMeasure, 
       OWS.CurrentStatus, 
       LS.Location, 
       LS.ProgramStamp_LocalTime
FROM table1 AS OWS
INNER JOIN table2 AS LS
ON OWS.Q2CNum = LS.[Order]
AND OWS.LineItem = LS.LineItem
AND OWS.Identifier = LS.Identifier
WHERE LS.ProgramStamp_LocalTime = 
    (Select TOP 1 LS2.ProgramStamp_LocalTime
    from dbo.view_Location_Stamps_Local AS LS2 
    Where LS2.[Order] = OWS.Q2CNum
    AND LS2.LineItem= OWS.LineItem
    AND LS2.Identifier = OWS.Identifier
    Order by LS2.ProgramStamp_LocalTime Desc) 
AND LS.[Order] = @testVar
AND LS.LineItem = COALESCE('001',OWS.LineItem)
AND LS.Identifier like '%'
---Group by Q2CNum
ORDER BY Q2CNum, LineItem, Identifier DESC
FOR JSON Auto

我需要的目标层级JSON结构如下：

{
    "Q2CNum": "value",
    "LineItems": [{
         "LineItem": "value",
         "Identifiers": [{
               "identifier": "value",
               "etc": "..."
         }]
    }]
}

更新2 #

调整后的查询可以输出更接近需求的结果，但耗时更长，代码如下：

Select Q2C.Q2CNum, 
       Li.LineItem  , 
       OWS.Identifier, 
       OWS.ProductType, 
       OWS.Workstation, 
       OWS.Calculation, 
       OWS.UnitOfMeasure, 
       OWS.CurrentStatus, 
       LS.Location, 
       LS.ProgramStamp_LocalTime
FROM table1 AS OWS
INNER JOIN table2 AS Q2C
ON Q2C.Q2CNum = OWS.Q2CNum and Q2C.LineItem = OWS.LineItem and Q2C.Identifier = OWS.Identifier and Q2C.Workstation = OWS.Workstation
INNER JOIN table3 AS Li
ON Li.Q2CNum = OWS.Q2CNum and Li.LineItem = OWS.LineItem and Li.Identifier = OWS.Identifier and Li.Workstation = OWS.Workstation
INNER JOIN table4 AS LS
ON OWS.Q2CNum = LS.[Order]
AND OWS.LineItem = LS.LineItem
AND OWS.Identifier = LS.Identifier
WHERE LS.ProgramStamp_LocalTime = 
    (Select TOP 1 LS2.ProgramStamp_LocalTime
    from table5 AS LS2 
    Where LS2.[Order] = OWS.Q2CNum
    AND LS2.LineItem= OWS.LineItem
    AND LS2.Identifier = OWS.Identifier
    Order by LS2.ProgramStamp_LocalTime Desc) 
AND LS.[Order] = @testVar
AND LS.LineItem = COALESCE('001',OWS.LineItem)
AND LS.Identifier like '%'
---Group by Q2CNum
ORDER BY Q2CNum, LineItem, Identifier DESC
FOR JSON Auto

最终更新 #

Mike Petri的方案可以实现需求，我调整后最终采用先将查询结果存入临时表，再通过嵌套子查询生成层级JSON的方案，受资源限制我没有使用视图，临时表方案更适合当前场景，最终代码如下：

IF OBJECT_ID('tempdb..#Temp') IS NOT NULL
BEGIN
    DROP TABLE #Temp;
END;

SELECT  OWS.Q2CNum
        ,OWS.LineItem
        ,OWS.Identifier
        ,OWS.ProductType
        ,OWS.Workstation
        ,OWS.Calculation
        ,OWS.UnitOfMeasure
        ,OWS.CurrentStatus
        ,LS.Location
        ,LS.ProgramStamp_LocalTime
INTO    #Temp
FROM    table1 AS OWS
        INNER JOIN table2 AS LS ON OWS.Q2CNum = LS.[Order]
                                   AND OWS.LineItem = LS.LineItem
                                   AND OWS.Identifier = LS.Identifier
WHERE   LS.ProgramStamp_LocalTime = (
                                        SELECT  TOP 1
                                                LS2.ProgramStamp_LocalTime
                                        FROM    talbe3 AS LS2
                                        WHERE   LS2.[Order] = OWS.Q2CNum
                                                AND LS2.LineItem = OWS.LineItem
                                                AND LS2.Identifier = OWS.Identifier
                                        ORDER BY
                                                LS2.ProgramStamp_LocalTime DESC
                                    )
        AND LS.[Order] = @testVar
        AND LS.LineItem = COALESCE('001', OWS.LineItem)
        AND LS.Identifier LIKE '%';

----REAL MAGIC HAPPENS HERE

SELECT t.Q2CNum
        ,(
             SELECT DISTINCT(t2.LineItem)
                    ,(
                         SELECT  DISTINCT(t3.Identifier)
                                ,t3.Location
                                ,t3.ProgramStamp_LocalTime
                                ,(
                                    SELECT   t4.Workstation
                                            ,t4.ProductType
                                            ,t4.Calculation
                                            ,t4.UnitOfMeasure
                                            ,t4.CurrentStatus
                                    FROM #Temp AS t4
                                    WHERE t3.Q2CNum = t4.Q2CNum and t3.LineItem = t4.LineItem and  t3.Identifier = t4.Identifier
                                    FOR JSON PATH 
                                ) AS Workstations
                        FROM #Temp AS t3
                        WHERE t3.Q2CNum = t2.Q2CNum and t2.LineItem = t3.LineItem
                        FOR JSON PATH
                     ) AS Identifiers
             FROM   #Temp AS t2
             WHERE  t.Q2CNum = t2.Q2CNum
             FOR JSON PATH
         ) AS LineItems
FROM    (
            SELECT  DISTINCT
                    Q2CNum
            FROM    #Temp
        ) AS t
FOR JSON AUTO;
DROP TABLE #Temp;

欢迎大家提出优化建议，我还有一个疑问：嵌套查询的WHERE条件应该仅关联直接父层级表即可，还是需要额外关联更上层的父表？

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