รูปแบบ MySQL หนึ่งต่อหลายถึง JSON

นี่คือแบบสอบถาม SQL ที่อาจตรงตามความต้องการของคุณ โดยใช้ ฟังก์ชันรวม MySQL JSON_ARRAYAGG() เพื่อสร้างอาร์เรย์ของออบเจ็กต์ JSON (ซึ่งสร้างขึ้นโดยใช้ JSON_OBJECT())

การจัดกลุ่มระดับกลางจะดำเนินการภายในการรวม เพื่อสร้างอาร์เรย์ sales JSON ของผู้ใช้แต่ละคน จากนั้นผลลัพธ์จะถูกรวมเป็นบรรทัดเดียว โดยมีหนึ่งคอลัมน์ที่มีอาร์เรย์ JSON ที่เป็นผลลัพธ์ของออบเจ็กต์

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

สาธิตบน DB Fiddle

หากคุณล้อมค่าที่ส่งคืนด้วย JSON_PRETTY ผลลัพธ์จะเป็นดังนี้:

[
  {
    "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"
      }
    ]
  }
]

แก้ไข : นี่คือโซลูชัน (น่าเกลียด) สำหรับ MySQL ‹ 5.7 โดยที่ไม่รองรับ JSON มันอาศัยฟังก์ชันการจัดการสตริงเท่านั้น โปรดทราบว่าการดำเนินการนี้จะใช้ได้ตราบใดที่ช่อง varchar ไม่มีอักขระ " :

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

สาธิตเกี่ยวกับ DB Fiddle