title: How to generate hierarchical JSON from SQL Server data table
body: <p>I have sql server hierarchical data table with this sample structure and data:</p>
  
  <pre><code>Id      name     size     type      parrent_Id
  
  1       AAAA     2k        t1       null
  2       BB       2k        t2       1
  3       CC       1k        t3       1
  4       DDDD     2k        t4       null
  5       EE       2k        t5       4
  6       FF       1k        t6       5
  ...
  </code></pre>
  
  <p>i need sql query that generate JSON structure from my sqlserver table to use it in primeng treetable component. it requires json structure like this:</p>
  
  <pre><code>{
  "data":
  [
      {
          "data":{
              "name":"Documents",
              "size":"2k",
              "type":"Folder"
          },
          "children":[
              {
                  "data":{
                      "name":"Work",
                      "size":"5k",
                      "type":"Folder"
                  },
                  "children":[
                      {
                          "data":{
                              "name":"Expenses.doc",
                              "size":"30kb",
                              "type":"Document"
                          }
                      },
                      {
                          "data":{
                              "name":"Resume.doc",
                              "size":"25kb",
                              "type":"Resume"
                          }
                      }
                  ]
              },
              {
                  "data":{
                      "name":"Home",
                      "size":"20kb",
                      "type":"Folder"
                  },
                  "children":[
                      {
                          "data":{
                              "name":"Invoices",
                              "size":"20kb",
                              "type":"Text"
                          }
                      }
                  ]
              }
          ]
      },
      {
          "data":{
              "name":"Pictures",
              "size":"150kb",
              "type":"Folder"
          },
          "children":[
              {
                  "data":{
                      "name":"barcelona.jpg",
                      "size":"90kb",
                      "type":"Picture"
                  }
              },
              {
                  "data":{
                      "name":"primeui.png",
                      "size":"30kb",
                      "type":"Picture"
                  }
              },
              {
                  "data":{
                      "name":"optimus.jpg",
                      "size":"30kb",
                      "type":"Picture"
                  }
              }
          ]
      }
  ]}
  </code></pre>
  
  <p>link of primeng tree table component and related documentation is <a href="https://www.primefaces.org/primeng/#/treetable" rel="nofollow noreferrer">here</a>.
  also i'm using sql server 2017.</p>
  
  <p>thanks.</p>