guidance on SQL query

[Michael Williams]

Hi, I'm using AWS RDS MySQL 8.0.23 on Linux, and mysql workbench 8.0.30. I'm trying to generate results that combine data from multiple tables into a single row in the results. There are three tables involved, let's call them match and teammember, and user. I need to return a few fields from the match table, along with an array of the team members from the two teams playing in the match. The data is not stored in JSON in the tables. The format of the results as expressed in JSON should be:

{
"match" {
"matchid": "string",
"team1id": "string",
"team2id": "string",

},
"teammembers" {
"team1": [
{
"userinfo": {
"userid": "string",
"fullname": "string"
},
"picture": "string"
},
{
"userinfo": {
"userid": "string",
"fullname": "string"
},
"picture": "string"
}
],
"team2": [
{
"userinfo": {
"userid": "string",
"fullname": "string"
},
"picture": "string"
},
{
"userinfo": {
"userid": "string",
"fullname": "string"
},
"picture": "string"
}
]
}
}


The match table has matchid, team1id and team2id. The teammeber table has teamid and userid. The user table has userid, fullname and picture.

I cannot change the structure of the existing tables. So I'm trying to understand the appropriate query to create this row with the above format. I want to have a row for each match, with the corresponding team members info, structured as above. I suppose I could have multiple select statements and glue the results together. Is there a more sophisticated way to get the results? Many thanks!