MySQL query related entities from multiple child rows into json array

Question

This is my databasedesign with a many-to-many relation

table Tournament
TournamentId int
name varchar(45)

table User
UserId int
name varchar(45)

table Tournament_Users
Tournament_Id int
User_Id int

I am attempting to return all tournaments with a list of the tournament's users in a JSON format. I'm not sure if this is possible to do in the query or it's a javascript job? I tried to follow this Returning child rows formatted as JSON in SQL Server queries but I drowned in the example.

This is my current query

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

So as a json result this looks like this:

Which seems pretty difficult for a client to work with. How can I convert it into a format like this?

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

To format the output of the FOR JSON clause automatically based on the structure of the SELECT statement, specify the AUTO option. — Kedar Limaye
– Kedar Limaye, Commented May 2, 2018 at 11:23
Provide table structures (SHOW CREATE TABLE table) and example data for all table involved on sqlfiddle.com.. Then we can help you.. — Raymond Nijland
– Raymond Nijland, Commented May 2, 2018 at 11:34
Dear, you do not mention here the database name. i was effort for u on MSSQL but u remarks me bad. — M Danish
– M Danish, Commented May 3, 2018 at 5:28
I didn't downvote you but it says mysql in both the title and the tag. — J.Kirk.
– J.Kirk., Commented May 3, 2018 at 8:25

wchiquito · Accepted Answer · 2018-05-03 05:13:43Z

1

Try something like:

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`;

See db-fiddle.

answered May 3, 2018 at 5:13

wchiquito

16.6k2 gold badges37 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

J.Kirk. Over a year ago

thanks this works as intended. Keep in mind JSON_ARRAYAGG wasn't introduced until version 5.7.22

Kedar Limaye · Accepted Answer · 2018-05-02 12:14:52Z

0

If you are using mysql 8.0 then

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

You can refer -https://dev.mysql.com/doc/refman/8.0/en/json-creation-functions.html

answered May 2, 2018 at 12:14

Kedar Limaye

1,0418 silver badges15 bronze badges

3 Comments

Raymond Nijland Over a year ago

JSON_OBJECT isn't a MySQL 8.0 only function. JSON_OBJECT was already in MySQL in 5.7

J.Kirk. Over a year ago

This doesn't give me the format i desire as in the last image of my post instead it just gives me 4 rows in json format? Is there no way to squeeze it into 3 so I don't have duplicate rows for the same tournament? Thanks for your help

Kedar Limaye Over a year ago

you have to use json_array with json_object.please check this- stackoverflow.com/questions/37470949/…

Collectives™ on Stack Overflow

MySQL query related entities from multiple child rows into json array

2 Answers 2

1 Comment

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related