How to generate hierarchical JSON data with Microsoft SQL Server 2016?
<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>