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