How to aggregate data in JSON format?

Data:

Table: clients

id name   
1  Client One 
2  Client Two 
3  Client Three

Table: client_phone_numbers

id client_id phone_type number
1  1         home       +1 (012) 34567
2  1         work       +1 (012) 53219
3  2         home       +1 (012) 26974

Question: What can be used to receive JSON aggregated data from two tables?

The way to achieve desired result in PostgreSQL - using ROW_TO_JSON for generate JSON object from whole row data and JSON_AGG for aggregate rows to JSON array

  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;

PostgreSQL fiddle: https://sqlize.online/sql/psql12/bd6a22b130681214bd48f817b4ad6588/

In MySQL we can use JSON_OBJECT for generate JSON object from named fields data and JSON_ARRAYAGG for aggregate rows to JSON array

  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;

MySQL fiddle: https://sqlize.online/sql/mysql57/9dc33f3fb12ca567324859c7cf8def1e/

Modern SQL Server versions provide "magic" FOR JSON AUTO that generate JSON output without any data manipulation

  SELECT *
  FROM clients 
  LEFT JOIN client_phonenumbers ON client_phonenumbers.client_id = clients.id
  FOR JSON AUTO;

MS SQL fiddle: https://sqlize.online/sql/mssql2017/c368008f0849aae2973fddbb8071d147/