title: How to generate hierarchical JSON data with Microsoft SQL Server 2016?
body: <p>I'm using <strong>Microsoft SQL Server 2016</strong>. This version supports JSON.</p>
  <p>I have a <strong>Person</strong> table with the following data:</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>PersonId</th>
  <th>FatherId</th>
  <th>Name</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>1</td>
  <td>NULL</td>
  <td>4th Grand Father</td>
  </tr>
  <tr>
  <td>2</td>
  <td>1</td>
  <td>3rd Grand Father</td>
  </tr>
  <tr>
  <td>3</td>
  <td>2</td>
  <td>2nd Grand Father</td>
  </tr>
  <tr>
  <td>4</td>
  <td>3</td>
  <td>Grand Father</td>
  </tr>
  <tr>
  <td>5</td>
  <td>4</td>
  <td>Father</td>
  </tr>
  <tr>
  <td>6</td>
  <td>4</td>
  <td>Uncle</td>
  </tr>
  <tr>
  <td>7</td>
  <td>6</td>
  <td>Cousin</td>
  </tr>
  <tr>
  <td>8</td>
  <td>5</td>
  <td>Brother</td>
  </tr>
  <tr>
  <td>9</td>
  <td>5</td>
  <td>Me</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>I run the following query:</p>
  <pre class="lang-sql prettyprint-override"><code>WITH Persons_CTE AS(
      SELECT PersonId, FatherId, Name FROM Persons WHERE FatherId IS NULL
      UNION ALL
      SELECT P.PersonId, P.FatherId, P.Name FROM Persons P JOIN Persons_CTE PCTE
      ON PCTE.PersonId = P.FatherId)
  
      SELECT P.Name as Name, PCTE.Name as Children FROM Persons_CTE PCTE LEFT JOIN Persons P
      ON P.PersonId = PCTE.FatherId 
      FOR JSON PATH
  </code></pre>
  <p>The query generates the following result:</p>
  <pre class="lang-json prettyprint-override"><code>[
     {
        &quot;Children&quot;:&quot;4th Grand Father&quot;
     },
     {
        &quot;Name&quot;:&quot;4th Grand Father&quot;,
        &quot;Children&quot;:&quot;3rd Grand Father&quot;
     },
     {
        &quot;Name&quot;:&quot;3rd Grand Father&quot;,
        &quot;Children&quot;:&quot;2nd Grand Father&quot;
     },
     {
        &quot;Name&quot;:&quot;2nd Grand Father&quot;,
        &quot;Children&quot;:&quot;Grand Father&quot;
     },
     {
        &quot;Name&quot;:&quot;Grand Father&quot;,
        &quot;Children&quot;:&quot;Father&quot;
     },
     {
        &quot;Name&quot;:&quot;Grand Father&quot;,
        &quot;Children&quot;:&quot;Uncle&quot;
     },
     {
        &quot;Name&quot;:&quot;Uncle&quot;,
        &quot;Children&quot;:&quot;Cousin&quot;
     },
     {
        &quot;Name&quot;:&quot;Father&quot;,
        &quot;Children&quot;:&quot;Brother&quot;
     },
     {
        &quot;Name&quot;:&quot;Father&quot;,
        &quot;Children&quot;:&quot;Me&quot;
     }
  ]
  </code></pre>
  <p>I want the query result to the following hierarchical format. How can I do it?</p>
  <pre class="lang-json prettyprint-override"><code>[
     {
        &quot;Name&quot;:&quot;4th Grand Father&quot;,
        &quot;Children&quot;:[
           {
              &quot;Name&quot;:&quot;3rd Grand Father&quot;,
              &quot;Children&quot;:[
                 {
                    &quot;Name&quot;:&quot;2nd Grand Father&quot;,
                    &quot;Children&quot;:[
                       {
                          &quot;Name&quot;:&quot;Grand Father&quot;,
                          &quot;Children&quot;:[
                             {
                                &quot;Name&quot;:&quot;Father&quot;,
                                &quot;children&quot;:[
                                   {
                                      &quot;Name&quot;:&quot;Brother&quot;
                                   },
                                   {
                                      &quot;Name&quot;:&quot;Me&quot;
                                   }
                                ]
                             },
                             {
                                &quot;Name&quot;:&quot;Uncle&quot;,
                                &quot;children&quot;:[
                                   {
                                      &quot;Name&quot;:&quot;Cousin&quot;
                                   }
                                ]
                             }
                          ]
                       }
                    ]
                 }
              ]
           }
        ]
     }
  ]
  </code></pre>