Mysqlで重複した内容を、json形式でまとめ出力したい。

Question

###前提・実現したいこと
DBにある、列で重複している内容を、classの中にある値で、くくりだし、jsonの形式で出力したい。

###DBの中。
id studentnum class
1 1111 1
2 2222 1
3 3333 1
4 4444 2
5 5555 2

###出力したい内容
{
1:[
{id:1,
studentnum:1111
},
{id:2,
studentnum:2222
},
....
2:[
{id:4,
studentnum:4444
},
{id:5,
studentnum:5555
},
...
}

###試したこと
GRUOP BYやDISTINCTなど試しましたがうまくできませんでした。
GROUP BYの時に集計関数を使わず上記のようなことができないのでしょうか。

どなたかわかる方いたら、ご教授ください。お願い致します。

Answer 1

MySQLには GROUP_CONCAT なんて便利なものがあるんですね。で、調べてみました。
GROUP_CONCAT の引数には複数のフィールドを指定できるらしいので、
前の回答のものよりもう少しシンプルに記述できそうです。

SELECT CONCAT('{',GROUP_CONCAT(class, itm ORDER BY class),'}') AS json
FROM (
  SELECT class,
    CONCAT(':[',GROUP_CONCAT('{id:',id,',studentnum:',studentnum,'}' ORDER BY id),']') AS itm
  FROM tbl
  GROUP BY class
) AS jsn;

Comments

[退会済みユーザー]

ありがとうございます。

Answer 2

こういうのあまりSQLは得意では無いと思いますが、次のような形で一応動作するように思います。
※paizaで動作確認済み

create table tbl(id integer, studentnum integer, class integer);
insert into tbl(id, studentnum, class) values(1,1111,1);
insert into tbl(id, studentnum, class) values(2,2222,1);
insert into tbl(id, studentnum, class) values(3,3333,1);
insert into tbl(id, studentnum, class) values(4,4444,2);
insert into tbl(id, studentnum, class) values(5,5555,2);

SELECT CONCAT('{',GROUP_CONCAT(jsn.itm SEPARATOR ','),'}') AS json
FROM (
  SELECT CONCAT(class, ':[', GROUP_CONCAT(cls.itm SEPARATOR ','), ']') AS itm
  FROM (
    SELECT class, CONCAT('{', GROUP_CONCAT(dat.itm SEPARATOR ','), '}') AS itm
    FROM (
      SELECT class, id, CONCAT('id:',id,',studentnum:',studentnum) AS itm
      FROM tbl
    ) AS dat
    GROUP BY dat.class, dat.id
    ORDER BY dat.id
  ) AS cls
  GROUP BY cls.class
  ORDER BY cls.class
) AS jsn

Comments

[退会済みユーザー]

ありがとうございます。
確認できました。