Quite honestly, JOIN and JSON_ARRAYAGG are exactly what you need to get the data you want from multiple tables with the result as a JSON object. Find a good reference and dig in. It can be fun to learn this stuff! Maybe start here: https://www.tutorialspoint.com/mysql/index.htm