Sql-server – JSON aggregation in a self-join query

aggregatejsonsql server

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.

Best Answer

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"}                                                 |
|    |                        |           | ]                                                                                                                         |
+----+------------------------+-----------+---------------------------------------------------------------------------------------------------------------------------+

Related Solutions

SQL Server – Optimizing Table Order in Join Query

Yes, the results will be the same. Take a look at this as an example:

if exists(select * from sys.tables where name = 'T1')
    drop table T1
go
if exists(select * from sys.tables where name = 'T2')
    drop table T2
go

create table T1
(
    id int not null,
    someText varchar(100) not null
)
go

insert into T1
values(1, 'hello'),
(3, 'bye'),
(6, 'what')
go

create table T2
(
    id int not null,
    someText varchar (100) not null
)
go

insert into T2
values(2, 'hi'),
(3, 'ciao'),
(4, 'no')
go

select *
from T1
left join T2
on T1.id = T2.id

select *
from T2
right join T1
on T2.id = T1.id

The output will have the same exact fields (mind you, if you use SELECT * the column order will be different between the queries) with the same exact data.

As a reference, here are the two execution plans:

enter image description here

Mysql – group by clause without aggregate function

This:

SELECT users.* FROM users
INNER JOIN timesheets ON timesheets.user_id = users.id
WHERE (timesheets.submitted_at <= '2010-07-06 15:27:05.117700')
GROUP BY users.id

Finds all users who have a timesheet submitted on or before the given date. It's equivalent to:

SELECT DISTINCT users.* FROM users
INNER JOIN timesheets ON timesheets.user_id = users.id
WHERE (timesheets.submitted_at <= '2010-07-06 15:27:05.117700');

or:

SELECT  users.*
FROM users
WHERE EXISTS (
    SELECT 1
    FROM timesheets 
    WHERE timesheets.user_id = users.id
    AND timesheets.submitted_at <= '2010-07-06 15:27:05.117700'
);

It works because users.id is the primary key, so all other fields of users are functionally dependent on it. PostgreSQL knows that you don't have to use an aggregate to guarantee a single unambiguous result for each field in a row because there can only be one candidate users.name or whatever for any given users.id row.

(Older PostgreSQL versions didn't know how to identify functional dependencies of the primary key and and would throw an ERROR about needing to use an aggregate or include the field in the GROUP BY here).

Best Answer

Related Solutions

SQL Server – Optimizing Table Order in Join Query

Mysql – group by clause without aggregate function

Related Question