SQLize Online / PHPize Online  /  SQLtest Online

-- Hint: use Ctrl+Enter for SQL autocomplete CREATE TABLE clients ( id int auto_increment primary key, name varchar(64) ); INSERT INTO clients VALUES (1, 'Client One'), (2, 'Client Two'), (3, 'Client Three'); CREATE TABLE client_phonenumbers ( id int auto_increment primary key, client_id int, phone_type varchar(64), number varchar(64), FOREIGN KEY (client_id) REFERENCES clients(id) ); INSERT INTO client_phonenumbers VALUES (1, 1, 'home', '+1 (012) 34567'), (2, 1, 'work', '+1 (012) 53219'), (3, 2, 'home', '+1 (012) 26974'); SELECT JSON_OBJECT( 'name', client.name, 'phonenumbers', client.phonenumbers ) json_data FROM ( SELECT clients.name, JSON_ARRAYAGG( JSON_OBJECT( 'phone_type', client_phonenumbers.phone_type, 'number', client_phonenumbers.number ) ) phonenumbers FROM clients LEFT JOIN client_phonenumbers ON client_phonenumbers.client_id = clients.id GROUP BY clients.id, clients.name ) client ORDER BY client.name;