How can I get a full JSON without repeating keys in node-mysql?

Question

I'm trying to do an API for an application. This is my Mysql structure.

mysql> desc player;
+----------+-------------+------+-----+---------+----------------+
| Field    | Type        | Null | Key | Default | Extra          |
+----------+-------------+------+-----+---------+----------------+
| id       | int(11)     | NO   | PRI | NULL    | auto_increment |
| name     | varchar(30) | NO   | UNI | NULL    |                |
| password | varchar(30) | NO   |     | NULL    |                |
+----------+-------------+------+-----+---------+----------------+
3 rows in set (0,00 sec)

mysql> desc team;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| name  | varchar(30) | NO   |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0,00 sec)

mysql> desc player_team;
+--------+---------+------+-----+---------+----------------+
| Field  | Type    | Null | Key | Default | Extra          |
+--------+---------+------+-----+---------+----------------+
| id     | int(11) | NO   | PRI | NULL    | auto_increment |
| player | int(11) | NO   | MUL | NULL    |                |
| team   | int(11) | NO   | MUL | NULL    |                |
+--------+---------+------+-----+---------+----------------+
3 rows in set (0,00 sec)

And I'm executing this node code with node-mysql package:

var express = require('express');
var router = express.Router();
var connection = require('../lib/mysql.js');

router.get('/', function(req, res, next) {
  var query = 'select p.name as player, t.name as team from player p join player_team pt on p.id=pt.player join team t on pt.team = t.id order by player;';
  connection.query(query , function(error, result){
    if (error){
      res.send(error);
    }
    else {
      res.json(result);
    }
  });
});

And it the next JSON:

[  
   {  
      "player":"arzeus",
      "team":"Origen"
   },
   {  
      "player":"haton",
      "team":"Fnatic"
   },
   {  
      "player":"haton",
      "team":"Origen"
   },
   {  
      "player":"loko",
      "team":"Fnatic"
   }
]

Which is the best way to get the player "haton" both teams in an array? I know I can do it manually, but i would like to know if there's already some way to do it automatically.

[  
   {  
      "player":"arzeus",
      "team":"Origen"
   },
   {  
      "player":"haton",
      "teams" {
        "team":"Fnatic",
        "team":"Origen"
      }
   },
   {  
      "player":"loko",
      "team":"Fnatic"
   }
]

Answer 1

The closest you'll get with MySQL is GROUP_CONCAT.

Note that your desired output isn't possible anyway (if you wish to use it in JavaScript) as an object can't have duplicate attribute names:

{
  "team":"Fnatic",
  "team":"Origen"
}

Did you mean an array of objects? Like this:

"teams": [
  {"team": "Fnatic"},
  {"team": "Origen"}
]

Or simply an array of teams:

"teams": ["Fnatic" "Origen"]

Anyway, to get something closer to what you're after, GROUP_CONCAT will end up giving you:

{  
  "player":"haton",
  "teams" "Fnatic,Origen"
}