SQL TO JSON query how can i triple root my table?

Viewed 20

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"
      }
    }
  ]
}
0 Answers
Related