Format MySQL Satu-ke-Banyak ke JSON

Berikut adalah kueri SQL yang mungkin memenuhi kebutuhan Anda. Ini menggunakan fungsi agregat MySQL JSON_ARRAYAGG() untuk menghasilkan array objek JSON (yang dibuat menggunakan JSON_OBJECT()).

Pengelompokan tingkat menengah dilakukan dalam gabungan, untuk menghasilkan larik sales JSON setiap pengguna. Kemudian hasilnya dikumpulkan menjadi satu baris, dengan satu kolom yang berisi array objek JSON yang dihasilkan.

SELECT
  JSON_ARRAYAGG(JSON_OBJECT('id', u.id, 'name', u.name, 'sales', s.sales))
FROM
    user u
    LEFT JOIN (
        SELECT 
            user, 
            JSON_ARRAYAGG(JSON_OBJECT('id', id, 'item', item)) sales 
        FROM sale 
        GROUP BY user
    ) s ON s.user = u.id

Demo di DB Fiddle

Jika Anda membungkus nilai kembalian dengan JSON_PRETTY, hasilnya adalah sebagai berikut :

[
  {
    "id": 1,
    "name": "User 1",
    "sales": [
      {
        "id": 1,
        "item": "t-shirt"
      },
      {
        "id": 2,
        "item": "jeans"
      }
    ]
  },
  {
    "id": 2,
    "name": "User 2",
    "sales": [
      {
        "id": 3,
        "item": "sweatpants"
      },
      {
        "id": 4,
        "item": "gloves"
      }
    ]
  }
]

Edit : berikut adalah solusi (jelek) untuk MySQL ‹ 5.7, yang dukungan JSON tidak tersedia. Itu hanya bergantung pada fungsi manipulasi string. Harap dicatat bahwa ini hanya akan berfungsi selama bidang varchar tidak mengandung karakter " :

SELECT
    CONCAT(
        '[', 
        GROUP_CONCAT( CONCAT( '{ "id":', u.id, ', "name":"', u.name, '", "sales":', s.sales, ' }' )  SEPARATOR ', ' ),
        ']'
    )
FROM 
    user u
    LEFT JOIN (
        SELECT 
            user, 
            CONCAT( 
               '[', 
                GROUP_CONCAT( CONCAT( '{ "id":', id, ', "item":"', item, '" }' ) SEPARATOR ', '),
                ']'
            ) sales 
    FROM sale
    GROUP BY user ) s ON s.user = u.id

Demo di DB Fiddle