title: How to create a multi-level json output using sql query in SQL Server 2016?
body: <p>Is that possible create a multi-level json string in SQL Server 2016 ? I have a table (patient_data) like this:</p>
  
  <p><a href="https://i.stack.imgur.com/axo8G.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/axo8G.png" alt="enter image description here"></a></p>
  
  <p>And what i want to do is to create json string output in SQL Server like this:</p>
  
  <pre><code>{
    "patient":[
      {
        "key":"A",
        "data":[
          {
            "name":"Amy Farha",
            "colored":"darkred",
            "avatar_url":"https://s3.amazonaws.com/uifaces/faces/twitter/ladylexy/128.jpg",
            "subtitle":"Vice President",
            "patientid":"qweqweqeqeq"
          },
          {
            "name":"Anies",
            "colored":"darkblue",
            "avatar_url":"https://s3.amazonaws.com/uifaces/faces/twitter/adhamdannaway/128.jpg",
            "subtitle":"Vice Chairman",
            "patientid":"avasdasdad"
          }
        ]
      },
      {
        "key":"B",
        "data":[
          {
            "name":"Bryan Adams",
            "colored":"darkgreen",
            "avatar_url":"https://randomuser.me/api/portraits/med/women/91.jpg",
            "subtitle":"Reggae Man",
            "patientid":"avasdasdad"
          }
        ]
      },
        {
          "key":"D",
          "data":[
            {
              "name":"David dummy",
              "colored":"darkgreen",
              "avatar_url":"https://randomuser.me/api/portraits/med/women/91.jpg",
              "subtitle":"Reggae Man",
              "patientid":"avasdasdad"
            }
          ]
        },
        {
          "key":"M",
          "data":[
            {
              "name":"Muhammad Adams",
              "colored":"darkgreen",
              "avatar_url":"https://randomuser.me/api/portraits/med/women/91.jpg",
              "subtitle":"Reggae Man",
              "patientid":"avasdasdad"
            }
          ]
        },
        {
          "key":"T",
          "data":[
            {
              "name":"Tere",
              "colored":"darkgreen",
              "avatar_url":"https://randomuser.me/api/portraits/med/women/91.jpg",
              "subtitle":"Reggae Man",
              "patientid":"avasdasdad"
            },
            {
              "name":"Tifanny",
              "colored":"darkblue",
              "avatar_url":"https://s3.amazonaws.com/uifaces/faces/twitter/adhamdannaway/128.jpg",
              "subtitle":"Vice Chairman",
              "patientid":"avasdasdad"
            }
          ]
        },
        {
          "key":"X",
          "data":[
            {
              "name":"Xavier",
              "colored":"darkgreen",
              "avatar_url":"https://randomuser.me/api/portraits/med/women/91.jpg",
              "subtitle":"Reggae Man",
              "patientid":"avasdasdad"
            }
          ]
        }
    ]
  }
  </code></pre>
  
  <p>**NOTE "key" is a grouping by the first letter of patient's name</p>
  
  <p>I try use json path but couldn't figure out in multi level case .I hope someone can help me</p>