MySQL从多个子行查询相关实体到json数组中

Question

这是我的具有多对多关系的数据库设计

table Tournament
TournamentId int
name varchar(45)

table User
UserId int
name varchar(45)

table Tournament_Users
Tournament_Id int
User_Id int

我正在尝试以 JSON 格式返回所有锦标赛以及锦标赛用户列表。我不确定这是否可以在查询中完成，或者它是一个 javascript 作业？我试图遵循这个Returning child rows formatted as JSON in SQL Server queries但我沉浸在这个例子中。

这是我当前的查询

SELECT t.TournamentId, t.Name as tourName, u.UserId, u.Name as userName
FROM  Tournament_Users tu
LEFT JOIN  Tournaments t ON tu.Tournament_Id = t.TournamentId
LEFT JOIN  Users u ON u.UserId = tu.User_Id

所以作为 json 结果，它看起来像这样:

这对于客户来说似乎很难合作。如何将其转换为这样的格式？

SQLFIDDLE:http://sqlfiddle.com/#!9/9bc2ebf/3

Answer 1

尝试如下:

SELECT
  CONCAT('{"data": [', GROUP_CONCAT(`json`), ']}') `json`
FROM (
  SELECT
    JSON_OBJECT(
      'TournamentId', `t`.`TournamentId`,
      'tourName', `t`.`Name`,
      'users', JSON_ARRAYAGG(
                 JSON_OBJECT(
                   'UserId', `u`.`UserId`,
                   'userName', `u`.`Name`
                 )
               )
    ) `json`
  FROM
    `Tournament_Users` `tu`
    LEFT JOIN
      `Tournaments` `t` ON `tu`.`Tournament_Id` = `t`.`TournamentId`
    LEFT JOIN
      `Users` `u` ON `u`.`UserId` = `tu`.`User_Id`
  GROUP BY `t`.`TournamentId`, `t`.`Name`
  ORDER BY `t`.`TournamentId`
) `der`;

参见db-fiddle .