現有資料表

假設現有 departments (部門) 和 employees (員工) 兩張 table 資料。

多筆查詢結果串接合併為一筆 JSON

departments 和 employees 是 一對多 (one-to-many) 的關係，使用 JOIN 的查詢結果則會以 employees 的資料數量取得對應的 rows。

SELECT
    departments.id AS departments_id,
    departments.name AS departments_name,
    employees.id AS employees_id,
    employees.name AS employees_name,
    employees.sex AS employees_sex,
    employees.last_day AS employees_last_day
FROM departments
    LEFT JOIN employees
        ON departments.id = employees.departments_id

在子查詢 (subquery) 使用 GROUP BY 搭配 GROUP_CONCAT() 即可將多筆查詢結果串接合併為一筆，在運用 CONCAT() 將資料串接成 JSON。

SELECT
    departments.id AS departments_id,
    departments.name AS departments_name,
    -- 子查詢
    (
        SELECT
            -- 將多筆查詢結果依 GROUP BY 分組合併為一筆 JSON
            CONCAT(
                '[',
                GROUP_CONCAT(
                    CONCAT('{"name":"', employees.name, '",'),
                    CONCAT('"sex":"', employees.sex, '",'),
                    -- 如果資料會有 NULL，必須使用 IFNULL() 排除，否則會導致整筆資料為 NULL
                    CONCAT('"last_day":"', IFNULL(employees.last_day, '0'), '"}')
                ),
                ']'
            )
        FROM employees
        WHERE employees.departments_id = departments.id
        -- 將不重覆的欄位分組
        GROUP BY employees.departments_id
    ) AS employees
FROM departments

GROUP_CONCAT() 串接長度限制

GROUP_CONCAT() 預設可串接最大長度 group_concat_max_len 為 1024 字元，可如下自訂可串接最大長度：

vim /etc/my.cnf

[mysqld]
# 自訂 GROUP_CONCAT() 可串接最大長度 (預設 1024 字元)
group_concat_max_len=10240

設定完須重啟 MySQL：

systemctl restart mysqld

參考

• MySQL把多个查询结果合并JSON – 攻城狮·正 (JSON 格式有誤)