SQLize Online / PHPize Online  /  SQLtest Online

-- Hint: use Ctrl+Enter for SQL autocomplete CREATE TABLE clients ( id serial, name varchar(64) ); INSERT INTO clients VALUES (1, 'Client One'), (2, 'Client Two'), (3, 'Client Three'); CREATE TABLE client_phonenumbers ( id serial, client_id int, phone_type varchar(64), number varchar(64) ); CREATE INDEX client_id_ix ON client_phonenumbers USING btree (client_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 row_to_json(client.*) FROM ( SELECT clients.name, json_agg( row_to_json(client_phonenumbers.*) ) 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;