title: How to generate json data in below hierarchical format using sql server 2016
body: <p>I have a single SQL table which contain Parent and child relationship.I would like to know how to generate json data in below hierarchical format using SQL query (SQL Server 2016). Please check my table structure and needed output json format.</p>
  
  <p>Note: I have already generated below json data using "java script" code but I need to generate this json data via a SQL Server 2016 query..</p>
  
  <p>SQL Server table with record:</p>
  
  <pre><code>Id  name    url icon    Parent
  ----------------------------------
  3   LGD NULL    fa fa-dashboard 0
  4   GLD NULL    fa fa-dashboard 0
  5   FWD NULL    fa fa-dashboard 0
  9   Order Outstanding   /ZohoDashboard/9    fa fa-dashboard 3
  11  Current Order Status    /ZohoDashboard/11   fa fa-dashboard 4
  19  HR Overview /ZohoDashboard/19   fa fa-dashboard 3
  20  HR Attrition    /ZohoDashboard/20   fa fa-dashboard 3
  21  Shipment Tracking   /ZohoDashboard/21   fa fa-dashboard 3
  23  D &amp; D Overview  /ZohoDashboard/23   fa fa-cogs  3
  26  HO  NULL    fa fa-dashboard 0
  27  Leather Stock Overview  /ZohoDashboard/27   fa fa-cogs  4
  28  Leather PO Pending Details  /ZohoDashboard/28   fa fa-cogs  4
  29  HR Overview /ZohoDashboard/29   fa fa-cogs  4
  30  D &amp; D Production details    /ZohoDashboard/30   fa fa-cogs  4
  31  Payment receivable details  /ZohoDashboard/31   fa fa-cogs  26
  42  HnS NULL    fa fa-dashboard 0
  43  H &amp; S Sales Details /ZohoDashboard/43   fa fa-cogs  42
  44  H &amp; S Shoes Factory Order Details   /ZohoDashboard/44   fa fa-cogs  42
  45  KHRS    NULL    fa fa-dashboard 0
  46  Greenfield Sales Details    /ZohoDashboard/46   fa fa-cogs  45
  47  Stock Overview  /ZohoDashboard/47   fa fa-cogs  45
  48  Stock Details   /ZohoDashboard/48   fa fa-cogs  42
  49  Break Even Overview /ZohoDashboard/49   fa fa-cogs  45
  50  Camp Sales Details  /ZohoDashboard/50   fa fa-cogs  45
  51  Order Overview  /ZohoDashboard/51   fa fa-cogs  42
  52  Order Overview  /ZohoDashboard/52   fa fa-cogs  5
  53  Attendance Overview /ZohoDashboard/53   fa fa-cogs  4
  54  HR Overview /ZohoDashboard/54   fa fa-cogs  5
  55  Order In Value Overview /ZohoDashboard/55   fa fa-cogs  3
  56  Leather Stock Details   /ZohoDashboard/56   fa fa-cogs  5
  57  tt  /ZohoDashboard  fa fa-cogs  0
  58  HR Overview /ZohoDashboard/58   fa fa-cogs  57
  15  User Management NULL    fa fa-user  0
  16  Menu Master /MenuList   fa fa-user  15
  17  User Access /UserList   fa fa-user  15
  18  Division Master NULL    fa fa-user  15
  25  User List   NULL    fa fa-cogs  15
  </code></pre>
  
  <p>I need Json output format like this:</p>
  
  <pre><code>{
      "items": [
          {
              "Id": 3,
              "name": "LGD",
              "url": null,
              "icon": "fa fa-dashboard",
              "Parent": 0,
              "children": [
                  {
                      "Id": 9,
                      "name": "Order Outstanding",
                      "url": "/ZohoDashboard/9",
                      "icon": "fa fa-dashboard",
                      "Parent": 3
                  },
                  {
                      "Id": 19,
                      "name": "HR Overview",
                      "url": "/ZohoDashboard/19",
                      "icon": "fa fa-dashboard",
                      "Parent": 3
                  },
                  {
                      "Id": 20,
                      "name": "HR Attrition",
                      "url": "/ZohoDashboard/20",
                      "icon": "fa fa-dashboard",
                      "Parent": 3
                  },
                  {
                      "Id": 21,
                      "name": "Shipment Tracking",
                      "url": "/ZohoDashboard/21",
                      "icon": "fa fa-dashboard",
                      "Parent": 3
                  },
                  {
                      "Id": 23,
                      "name": "D &amp; D Overview",
                      "url": "/ZohoDashboard/23",
                      "icon": "fa fa-cogs",
                      "Parent": 3
                  },
                  {
                      "Id": 55,
                      "name": "Order In Value Overview",
                      "url": "/ZohoDashboard/55",
                      "icon": "fa fa-cogs",
                      "Parent": 3
                  }
              ]
          },
          {
              "Id": 4,
              "name": "GLD",
              "url": null,
              "icon": "fa fa-dashboard",
              "Parent": 0,
              "children": [
                  {
                      "Id": 11,
                      "name": "Current Order Status",
                      "url": "/ZohoDashboard/11",
                      "icon": "fa fa-dashboard",
                      "Parent": 4
                  },
                  {
                      "Id": 27,
                      "name": "Leather Stock Overview",
                      "url": "/ZohoDashboard/27",
                      "icon": "fa fa-cogs",
                      "Parent": 4
                  },
                  {
                      "Id": 28,
                      "name": "Leather PO Pending Details",
                      "url": "/ZohoDashboard/28",
                      "icon": "fa fa-cogs",
                      "Parent": 4
                  },
                  {
                      "Id": 29,
                      "name": "HR Overview",
                      "url": "/ZohoDashboard/29",
                      "icon": "fa fa-cogs",
                      "Parent": 4
                  },
                  {
                      "Id": 30,
                      "name": "D &amp; D Production details",
                      "url": "/ZohoDashboard/30",
                      "icon": "fa fa-cogs",
                      "Parent": 4
                  },
                  {
                      "Id": 53,
                      "name": "Attendance Overview",
                      "url": "/ZohoDashboard/53",
                      "icon": "fa fa-cogs",
                      "Parent": 4
                  }
              ]
          },
          {
              "Id": 5,
              "name": "FWD",
              "url": null,
              "icon": "fa fa-dashboard",
              "Parent": 0,
              "children": [
                  {
                      "Id": 52,
                      "name": "Order Overview",
                      "url": "/ZohoDashboard/52",
                      "icon": "fa fa-cogs",
                      "Parent": 5
                  },
                  {
                      "Id": 54,
                      "name": "HR Overview",
                      "url": "/ZohoDashboard/54",
                      "icon": "fa fa-cogs",
                      "Parent": 5
                  },
                  {
                      "Id": 56,
                      "name": "Leather Stock Details",
                      "url": "/ZohoDashboard/56",
                      "icon": "fa fa-cogs",
                      "Parent": 5
                  }
              ]
          },
          {
              "Id": 26,
              "name": "HO",
              "url": null,
              "icon": "fa fa-dashboard",
              "Parent": 0,
              "children": [
                  {
                      "Id": 31,
                      "name": "Payment receivable details",
                      "url": "/ZohoDashboard/31",
                      "icon": "fa fa-cogs",
                      "Parent": 26
                  }
              ]
          },
          {
              "Id": 42,
              "name": "HnS",
              "url": null,
              "icon": "fa fa-dashboard",
              "Parent": 0,
              "children": [
                  {
                      "Id": 43,
                      "name": "H &amp; S Sales Details",
                      "url": "/ZohoDashboard/43",
                      "icon": "fa fa-cogs",
                      "Parent": 42
                  },
                  {
                      "Id": 44,
                      "name": "H &amp; S Shoes Factory Order Details",
                      "url": "/ZohoDashboard/44",
                      "icon": "fa fa-cogs",
                      "Parent": 42
                  },
                  {
                      "Id": 48,
                      "name": "Stock Details",
                      "url": "/ZohoDashboard/48",
                      "icon": "fa fa-cogs",
                      "Parent": 42
                  },
                  {
                      "Id": 51,
                      "name": "Order Overview",
                      "url": "/ZohoDashboard/51",
                      "icon": "fa fa-cogs",
                      "Parent": 42
                  }
              ]
          },
          {
              "Id": 45,
              "name": "KHRS",
              "url": null,
              "icon": "fa fa-dashboard",
              "Parent": 0,
              "children": [
                  {
                      "Id": 46,
                      "name": "Greenfield Sales Details",
                      "url": "/ZohoDashboard/46",
                      "icon": "fa fa-cogs",
                      "Parent": 45
                  },
                  {
                      "Id": 47,
                      "name": "Stock Overview",
                      "url": "/ZohoDashboard/47",
                      "icon": "fa fa-cogs",
                      "Parent": 45
                  },
                  {
                      "Id": 49,
                      "name": "Break Even Overview",
                      "url": "/ZohoDashboard/49",
                      "icon": "fa fa-cogs",
                      "Parent": 45
                  },
                  {
                      "Id": 50,
                      "name": "Camp Sales Details",
                      "url": "/ZohoDashboard/50",
                      "icon": "fa fa-cogs",
                      "Parent": 45
                  }
              ]
          },
          {
              "Id": 57,
              "name": "tt",
              "url": "/ZohoDashboard",
              "icon": "fa fa-cogs",
              "Parent": 0,
              "children": [
                  {
                      "Id": 58,
                      "name": "HR Overview",
                      "url": "/ZohoDashboard/58",
                      "icon": "fa fa-cogs",
                      "Parent": 57
                  }
              ]
          },
          {
              "Id": 15,
              "name": "User Management",
              "url": null,
              "icon": "fa fa-user",
              "Parent": 0,
              "children": [
                  {
                      "Id": 16,
                      "name": "Menu Master",
                      "url": "/MenuList",
                      "icon": "fa fa-user",
                      "Parent": 15
                  },
                  {
                      "Id": 17,
                      "name": "User Access",
                      "url": "/UserList",
                      "icon": "fa fa-user",
                      "Parent": 15
                  },
                  {
                      "Id": 18,
                      "name": "Division Master",
                      "url": null,
                      "icon": "fa fa-user",
                      "Parent": 15
                  },
                  {
                      "Id": 25,
                      "name": "User List",
                      "url": null,
                      "icon": "fa fa-cogs",
                      "Parent": 15
                  }
              ]
          }
      ]
  }
  </code></pre>
  
  <p>This is my SQL query:</p>
  
  <pre><code>select 
      PARENT.MenuId Id, PARENT.MenuName name, NULL url, 
      PARENT.IconCSS icon, PARENT.ParentId parent ,
      (Select 
           MenuId Id, MenuName name, 
           Case 
              When MenuParam = 'Y' And ParentId &lt;&gt; 0 
                 Then RTrim(Page) + '/' + RTrim(MenuId) 
                 Else RTRIM(Page)  
           End url, iconcss icon, ParentId parent
       from 
           MenuMaster AS Child
       where 
           (CHILD.ParentId &gt; 0) And CHILD.MenuId = PARENT.MenuId  
       FOR JSON AUTO) as Children
  from 
      MenuMaster AS PARENT
  FOR JSON PATH, ROOT ('items') 
  </code></pre>
  
  <p>Current output of json data from this query:</p>
  
  <pre><code>{"items":[{"Id":3,"name":"LGD","icon":"fa fa-dashboard","parent":0},{"Id":4,"name":"GLD","icon":"fa fa-dashboard","parent":0},{"Id":5,"name":"FWD","icon":"fa fa-dashboard","parent":0},{"Id":9,"name":"Order Outstanding","icon":"fa fa-dashboard","parent":3,"Children":[{"Id":9,"name":"Order Outstanding","url":"\/ZohoDashboard\/9","icon":"fa fa-dashboard","parent":3}]},{"Id":11,"name":"Current Order Status","icon":"fa fa-dashboard","parent":4,"Children":[{"Id":11,"name":"Current Order Status","url":"\/ZohoDashboard\/11","icon":"fa fa-dashboard","parent":4}]},{"Id":15,"name":"User Management","icon":"fa fa-user","parent":0},{"Id":16,"name":"Menu Master","icon":"fa fa-user","parent":15,"Children":[{"Id":16,"name":"Menu Master","url":"\/MenuList","icon":"fa fa-user","parent":15}]},{"Id":17,"name":"User Access","icon":"fa fa-user","parent":15,"Children":[{"Id":17,"name":"User Access","url":"\/UserList","icon":"fa fa-user","parent":15}]},{"Id":18,"name":"Division Master","icon":"fa fa-user","parent":15,"Children":[{"Id":18,"name":"Division Master","icon":"fa fa-user","parent":15}]},{"Id":19,"name":"HR Overview","icon":"fa fa-dashboard","parent":3,"Children":[{"Id":19,"name":"HR Overview","url":"\/ZohoDashboard\/19","icon":"fa fa-dashboard","parent":3}]},{"Id":20,"name":"HR Attrition","icon":"fa fa-dashboard","parent":3,"Children":[{"Id":20,"name":"HR Attrition","url":"\/ZohoDashboard\/20","icon":"fa fa-dashboard","parent":3}]},{"Id":21,"name":"Shipment Tracking","icon":"fa fa-dashboard","parent":3,"Children":[{"Id":21,"name":"Shipment Tracking","url":"\/ZohoDashboard\/21","icon":"fa fa-dashboard","parent":3}]},{"Id":23,"name":"D &amp; D Overview","icon":"fa fa-cogs","parent":3,"Children":[{"Id":23,"name":"D &amp; D Overview","url":"\/ZohoDashboard\/23","icon":"fa fa-cogs","parent":3}]},{"Id":25,"name":"User List","icon":"fa fa-cogs","parent":15,"Children":[{"Id":25,"name":"User List","icon":"fa fa-cogs","parent":15}]},{"Id":26,"name":"HO","icon":"fa fa-dashboard","parent":0},{"Id":27,"name":"Leather Stock Overview"
  </code></pre>