Currently I'm running this SQL SELECT statement
SELECT id,
(SELECT t2.att1 AS [ Anul ],
t3.att2 AS [ Strur.Nuer ],
t3.att3 AS [ Strur.Tyvn ] etc ..
FROM table1 AS t2
LEFT JOIN table2 AS t3
WHERE t1.number = t2.number
FOR JSON PATH, ROOT('Stur'))
FROM table1 AS t1
This result gives med Stur as the root of my JSON but I need Stur rooted within Udsling and then rooted within Sang. Is there an easy way to triple root SQL to JSON?
My wanted result
{
"Sang": {
"Udsling": {
"Stur": [
{
"Anul": "15",
"Strur": {
"Nuer": "652228",
"Tyvn": "yeet",
"Vyn": "false",
"Sel": "false",
"Oplse": "false"
}
},
{
"Anul": "50",
"Strur": {
"Nuer": "600",
"Tyvn": "yeee2",
"Vyn": "false",
"Sel": "false",
"Oplse": "false"
}
}
]
}
}
}
Current result
{
"Stur": [
{
"Anul": "15",
"Strur": {
"Nuer": "652228",
"Tyvn": "yeet",
"Vyn": "false",
"Sel": "false",
"Oplse": "false"
}
},
{
"Anul": "50",
"Strur": {
"Nuer": "600",
"Tyvn": "yeee2",
"Vyn": "false",
"Sel": "false",
"Oplse": "false"
}
}
]
}