sql - 将 SQL 输出格式化为 JSON 的最佳方法？

问题描述

一直在尝试进行 SQL 查询并输出完美的 JSON，但我遇到了问题。查询仅涉及表，当我使用“For JSON Auto”时，它会生成一个平面对象数组：在此处输入图像描述

但是有层次关系：

Q2CNum--- LineItems---- 标识符

我尝试使用“For JSON Path”，但它只构建每个 json 对象（例如，它不会聚合其父 LineItem 下的所有标识符）。

我已经设法通过将表连接到自身来使它看起来像这样（相同的查询只是额外的连接，“For JSON Path Auto”似乎像这样）它看起来更像这样：在此处输入图像描述

哪个好。问题是查询花费了将近 20 倍的时间（2 秒对 20 秒）。

也许我的自我加入写得不好，或者加入以获得这些结果是低效的。

无论如何，如果有人知道如何做到这一点，请告诉我（不能分享太多，我敢肯定你注意到图像上的颜色遮挡）。

更新：

这是查询的图片在此处输入图像描述。问题是我得到了一个扁平的 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

但我需要它是分层的：

{
    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 的解决方案有效。我不得不修补它并试图围绕它（仍然这样做 - 在此之前我对我的 SQL 技能更有信心）。无论如何，这是我使用的最终代码（上半部分并不重要，我已经编写了该查询，唯一重要的是使用临时表。我会使用视图但我有资源限制，所以这是更好的自动取款机）。真正的魔力始于 Query 的下半部分：

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 子句）同心 SQL 选择的正确方法，即表 t4 是否应该只过滤到表 t3（它是直接的层次结构父级？）或者我应该也向 t4 添加约束和 t2? 还是 t4 和 t?

标签： sqljsonsql-server-2017

sql - 将 SQL 输出格式化为 JSON 的最佳方法？

问题描述

解决方案

推荐阅读