JSON aggregation in a self-join query I have a database in SQL Server. I need to make a join query on a table itself, the menutable.

However, the left table is missing altogether from the result set. I only get the results from the right table.

Also I cannot aggregate the right table into an array like object as I can do in PostgreSQL and cannot have a group byed left table id result set, I use group by for left table id.

I can achieve this in PostgreSQL I do the following query and get what I expect and want. I make use of PostgreSQL's json_agg aggregate function:

select mt1.*, json_agg(mt2.*) 
from menutable mt1
join menutable mt2 on mt1.id = mt2.üstmenuid
group by mt1.id;


Result:

    {
      "results": [
        {
          "id": 4,
          "menuadi": "Ortak Haberler",
          "üstmenuid": null,
          "json_agg": [
            {
              "id": 5,
              "menuadi": "Lorem ipsum dolor sit amet, consectetur adipiscing elit",
              "üstmenuid": 4
            },
            {
              "id": 6,
              "menuadi": "Sed eget ipsum sit amet massa varius commodo",
              "üstmenuid": 4
            }
          ]
        },
        {
          "id": 1,
          "menuadi": "Ortak Konulu Mansetler",
          "üstmenuid": null,
          "json_agg": [
            {
              "id": 2,
              "menuadi": "In hac habitasse platea dictumst",
              "üstmenuid": 1
            },
            {
              "id": 3,
              "menuadi": "Nullam molestie augue sit amet magna bibendum imperdiet.",
              "üstmenuid": 1
            }
          ]
        }
      ]
    }


In SQL Server, I run the following, almost identical query apart form table names cases, lacking an aggregate function for the rows from right table:

SELECT  mt1.*, mt2.* 
FROM dbo.MenuTable AS mt1
JOIN dbo.MenuTable AS mt2 ON mt1.ID = mt2.UstMenuID;
GROUP BY mt1.ID, mt1.MenuAdi, mt1.UstMenuID, mt2.ID, mt2.MenuAdi, mt2.UstMenuID


Result set (left table is missing altogether):

 {
  "results": [
    {
      "ID": 2,
      "MenuAdi": "In hac habitasse platea dictumst",
      "UstMenuID": 1
    },
    {
      "ID": 3,
      "MenuAdi": "Nullam molestie augue sit amet magna bibendum imperdiet.",
      "UstMenuID": 1
    },
    {
      "ID": 5,
      "MenuAdi": "Lorem ipsum dolor sit amet, consectetur adipiscing elit",
      "UstMenuID": 4
    },
    {
      "ID": 6,
      "MenuAdi": "Sed eget ipsum sit amet massa varius commodo",
      "UstMenuID": 4
    }
  ]
}


What aggregation method should I do to have my intended result form the JOIN I have constructed in above PostgreSQL example?

Note: Menu names differ a bit in terms of case. Now I am trying to use Sequelize, but it gives the warning SQL Server does not support associating a table to itself, which I need to have JOIN.
postgres: http://rextester.com/TAYO71756

create table menutable (
    id int primary key
  , menuadi text
  , ustmenuid int null references menutable (id)
);
  insert into menutable values
 (1,'Ortak Konulu Mansetler',null)
,(2,'Kayseri Deki Bombalı Saldırının Yansımaları',1)
,(3,'Sayın Cumhurbaşkanımızın Trabzon Daki Açıklamalarının Yansımaları',1)
,(4,'Ortak Haberler',null)
,(5,'Sayın Hanımefendinin Trabzonda Şehit Aileleriyle Bir Araya Gelmesinin Yansımaları',4)
,(6,'Türk Meslektaşlarımızın Yanındayız',4);

select mt1.*, json_agg(mt2.*) 
from menutable mt1
join menutable mt2 on mt1.id = mt2.ustmenuid
group by mt1.id;


postgres results

+----+------------------------+-----------+-----------------------------------------------------------------------------------------------------------------------+
| id |        menuadi         | ustmenuid |                                                       json_agg                                                        |
+----+------------------------+-----------+-----------------------------------------------------------------------------------------------------------------------+
|  1 | Ortak Konulu Mansetler | NULL      | [{"id":2,"menuadi":"Kayseri Deki Bombalı Saldırının Yansımaları","ustmenuid":1}                                       |
|    |                        |           | , {"id":3,"menuadi":"Sayın Cumhurbaşkanımızın Trabzon Daki Açıklamalarının Yansımaları","ustmenuid":1}]               |
|  4 | Ortak Haberler         | NULL      | [{"id":5,"menuadi":"Sayın Hanımefendinin Trabzonda Şehit Aileleriyle Bir Araya Gelmesinin Yansımaları","ustmenuid":4} |
|    |                        |           | ,  {"id":6,"menuadi":"Türk Meslektaşlarımızın Yanındayız","ustmenuid":4}]                                             |
+----+------------------------+-----------+-----------------------------------------------------------------------------------------------------------------------+




sql server 2014: http://rextester.com/DPK57600

rextester is currently running sql server 2014, which does not have native json support. 
Instead, I will use dbo.FlattenedJSON() from Producing JSON Documents from SQL Server queries via TSQL - Phil Factor

create table menutable (
    id int primary key
  , menuadi nvarchar(128)
  , ustmenuid int null references menutable (id)
);
  insert into menutable values
 (1,'Ortak Konulu Mansetler',null)
,(2,'Kayseri Deki Bombalı Saldırının Yansımaları',1)
,(3,'Sayın Cumhurbaşkanımızın Trabzon Daki Açıklamalarının Yansımaları',1)
,(4,'Ortak Haberler',null)
,(5,'Sayın Hanımefendinin Trabzonda Şehit Aileleriyle Bir Araya Gelmesinin Yansımaları',4)
,(6,'Türk Meslektaşlarımızın Yanındayız',4);

select mt1.*, [json_agg]=dbo.FlattenedJson (
      ( select mt2.*
        from menutable mt2
        where mt2.ustmenuid = mt1.id
        for xml path, root
        )
    )
from menutable mt1
where mt1.ustmenuid is null


results:

+----+------------------------+-----------+---------------------------------------------------------------------------------------------------------------------------+
| id |        menuadi         | ustmenuid |                                                         json_agg                                                          |
+----+------------------------+-----------+---------------------------------------------------------------------------------------------------------------------------+
|  1 | Ortak Konulu Mansetler | NULL      | [                                                                                                                         |
|    |                        |           | {"id":"2","menuadi":"Kayseri Deki Bombali Saldirinin Yansimalari","ustmenuid":"1"},                                       |
|    |                        |           | {"id":"3","menuadi":"Sayin Cumhurbaskanimizin Trabzon Daki Açiklamalarinin Yansimalari","ustmenuid":"1"}                  |
|    |                        |           | ]                                                                                                                         |
|  4 | Ortak Haberler         | NULL      | [                                                                                                                         |
|    |                        |           | {"id":"5","menuadi":"Sayin Hanimefendinin Trabzonda Sehit Aileleriyle Bir Araya Gelmesinin Yansimalari","ustmenuid":"4"}, |
|    |                        |           | {"id":"6","menuadi":"Türk Meslektaslarimizin Yanindayiz","ustmenuid":"4"}                                                 |
|    |                        |           | ]                                                                                                                         |
+----+------------------------+-----------+---------------------------------------------------------------------------------------------------------------------------+