Сущности, связанные с запросом MySQL, из нескольких дочерних строк в массив json
Это мой дизайн базы данных с отношением "многие ко многим".
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? Я пытался следовать этому Возвращение дочерних строк в формате JSON в запросах SQL Server, но я утонул в этом примере.
Это мой текущий запрос
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
Предоставьте структуры таблиц (SHOW CREATE TABLE table) и примеры данных для всех таблиц, задействованных на sqlfiddle.com .. Тогда мы сможем вам помочь ..
Спасибо, отредактировал пост @RaymondNijland
Уважаемый, вы не указываете здесь имя базы данных. Я старался изо всех сил на MSSQL, но ты плохо меня замечаешь.
Я не голосовал против вас, но и в заголовке, и в теге написано mysql.
Ответы 2
Если вы используете mysql 8.0, тогда
SELECT json_object(
'TournamentId', t.TournamentId ,'tourName', t.Name ,'UserId' ,u.UserId, 'UserName' ,u.Name)
FROM Tournament_Users tu
LEFT JOIN Tournaments t ON tu.Tournament_Id = t.TournamentId
LEFT JOIN Users u ON u.UserId = tu.User_Id
Можете сослаться -https://dev.mysql.com/doc/refman/8.0/en/json-creation-functions.html
JSON_OBJECT - это не только функция MySQL 8.0. JSON_OBJECT уже был в MySQL в 5.7
Это не дает мне желаемого формата, как на последнем изображении моего сообщения, вместо этого он просто дает мне 4 строки в формате json? Нет возможности втиснуть его в 3, чтобы у меня не было повторяющихся строк для одного и того же турнира? Спасибо за вашу помощь
вы должны использовать json_array с json_object. проверьте это - stackoverflow.com/questions/37470949/…
Попробуйте что-нибудь вроде:
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.
спасибо, это работает как задумано. Имейте в виду, что JSON_ARRAYAGG не был представлен до версии 5.7.22.
Чтобы автоматически форматировать вывод предложения FOR JSON на основе структуры оператора SELECT, укажите параметр AUTO.