How to return a mysql JSON array of GROUP_CONCAT output?

Question

I have a table mapping departments and teams in MySQL. I want to retrieve array of teams for each department. For example, if I have two departments depA (teams teamAA, teamAB), depB (teams teamBA, teamBB, teamBC), I want to obtain following JSON

[
  {
    code: "depA",
    teams: ["teamAA", "teamAB"]
  },
  {
    code: "depB",
    teams: ["teamBA", "teamBB", "teamBC"]
  }
]

I am able to use GROUP_CONCAT to obtain a concatenated string but, I want a JSON array so that the sequelize.js library I am using can implicitly parse the entire data structure to js object.

Answer 1

It can be done like so,

SELECT `departmentCode` AS `code`, JSON_ARRAY(GROUP_CONCAT(DISTINCT `teamCode`)) AS `teams`