Been trying to take a SQL query and output the perfect JSON but I'm running into issues. The query merely involves to tables, when I use "For JSON Auto" it produces a flat array of objects: enter image description here
However there is a hierarchical relationship:
Q2CNum--- LineItems---- Identifier
I've attempted to use "For JSON Path" but it only structures each json object (it won't aggregate all Identifiers under their parent LineItem for example).
I've managed to get it to look like this by joining the table to itself (same query just extra joins, the "For JSON Path Auto" seems to like this) and it looks more like this:enter image description here
Which is good. Problem is the query takes almost 20 times as long (2seconds vs 20).
Perhaps my self joins are badly written, or joining to get these results is inefficient.
Anyway if anyone has a clue how to do this let me know (can't share too much more, I'm sure you noticed the color blockout on the images).
UPDATE:
Here's pic of the query enter image description here. The problem is I get a flat JSON (just an array of object).
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
But I need it to be hierarchal:
{
Q2CNum: value,
LineItems: [{
LineItem: value,
Identifiers: [{
identifier: value,
etc...
}],
}],
}
Update 2:
If I run this query I get more of what I want:
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
But the time for the query takes longer.
Final Update:
Ok so Mike Petri's solution works. I had to tinker with it and try to wrap my head around it (still doing that- I was a lot more confident in my SQL skills before this). In any case here is the final code I used (the top half doesn't really matter, I had written that query, the only importance there is using a temp table. I'd use a view BUT I have resource restrictions so this is better atm). The real magic begins at the bottom half of the 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;
All criticism welcome (one thing I'm wondering is the proper way to filter (with the Where clause) the concentric SQL selects i.e. should table t4 just filter to table t3 (it's immediate Hierarchal parent?) or should I also add constraints to t4 and t2? Or t4 and t?
See if this gets you closer. Being that you have joins involved, referencing inner/outer queries gets dicey. You could either make the base query a VIEW, and replace the #Temp references, or use the code below to store it in a temp table, then reference the outer table by the inner one to get the groupings you're after.
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 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 '%';
SELECT t.Q2CNum
,(
SELECT t2.LineItem
,(
SELECT t2.Identifier AS value
,t2.ProductType
,t2.Workstation
,t2.Calculation
,t2.UnitOfMeasure
,t2.CurrentStatus
,t2.Location
,t2.ProgramStamp_LocalTime
FOR JSON PATH
) AS Identifiers
FROM #Temp AS t2
WHERE t.Q2CNum = t2.Q2CNum
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
) AS LineItems
FROM (
SELECT DISTINCT
Q2CNum
FROM #Temp
) AS t
FOR JSON AUTO;
DROP TABLE #Temp;