MYSQL convert complex join outputs to JSON with Python

Question

This is an example MYSQL query for a tournament system.

SELECT
    tournament_game.game_identifier AS match_number,
    tournament_game.id AS match_id,
    tournament_game_round.round_number AS match_round_number,
    tournament_game_round_team.team_identifier,
    tournament_game_round_team_player.tournament_game_round_team_id,
    tournament_game_round_team_player.isHost,
    tournament_game_round_team_player.tournament_registration_id,
    user_platform_name.name AS player_name
FROM tournament_game
LEFT JOIN tournament_game_round ON tournament_game_round.tournament_game_id = tournament_game.id
LEFT JOIN tournament_game_round_team ON tournament_game_round_team.tournament_game_round_id = tournament_game_round.id
LEFT JOIN tournament_game_round_team_player ON tournament_game_round_team_player.tournament_game_round_team_id = tournament_game_round_team.id
LEFT JOIN tournament_registration ON tournament_registration.id = tournament_game_round_team_player.tournament_registration_id
LEFT JOIN user_platform_name ON user_platform_name.id = tournament_registration.user_platform_name_id
WHERE tournament_game.id = %s;

Which outputs:

1   264 0   0   125 1   66  playername1
1   264 1   0   127 0   66  playername1
1   264 0   1   126 0   69  playername2
1   264 1   1   128 1   69  playername2

I want the data to be formatted in JSON as:

{
match_id: a.match_id,
matches: [
    {
        match_number: a.match_number,
        match_rounds: [
            {
                match_round_number: a.match_round_number,
                match_round_teams: [
                    {
                        team_identifier: a.team_identifier,
                        team_players: [
                            {
                                player_name: a.player_name,
                                isHost: a.isHost,
                                rego_id: a.tournament_registration_id
                            }
                        ]
                    }
                ]
            }
        ]
    }
]
};

I can get the desired result with a very long ugly and complex python algorithm however I want to know if there is a pre-existing method of attaining the desired results easily. As I'm sure this is a common roadblock for learning developers like me.

If there is a better way to make the query I'm all ears.

MYSQL convert complex join outputs to JSON with Python

0 Answers0