如何在MySQL中查询关联表数据并返回嵌套对象数组？

表结构 #

主表 companyuser：

+----+------+---------------------+
| id | name | create_at           |
+----+------+---------------------+
|  1 | jack | 2023-04-26 22:01:37 |
|  2 | tom  | 2023-04-26 22:01:37 |
+----+------+---------------------+

关联表 company：

+----+----------------+--------+---------------------+
| id | companyuser_id | name   | create_at           |
+----+----------------+--------+---------------------+
|  1 |              1 | Amazon | 2023-04-26 22:47:21 |
+----+----------------+--------+---------------------+

关联表 feedback：

+----+------------+------------------------+---------------------+
| id | company_id | feedback               | create_at           |
+----+------------+------------------------+---------------------+
|  1 |          1 | i like this company    | 2023-04-26 22:58:04 |
|  2 |          1 | i dislike this company | 2023-04-26 22:58:04 |
+----+------------+------------------------+---------------------+

需求目标 #

需要生成如下格式的嵌套JSON对象数组：

[
    {
        "user": "jack",
        "companies": [
            {
                "name": "Amazon",
                "feedbacks": [
                    {
                        "id": 1,
                        "feedback": "i like this company"
                    },
                    {
                        "id": 2,
                        "feedback": "i dislike this company"
                    }
                ]
            }
        ]
    }
]

问题重现 #

尝试使用JSON_ARRAY和JSON_OBJECT编写SQL：

SELECT
    JSON_ARRAY(
        JSON_OBJECT(
            "user",
            cu.name,
            "companies",
            JSON_ARRAY(
                JSON_OBJECT(
                    "name",
                    c.name,
                    "feedbacks",
                    JSON_ARRAY(
                        JSON_OBJECT(
                            "feedback",
                            f.feedback,
                            "id",
                            f.id
                        )
                    )
                )
            )
        )
    )
FROM companyuser as cu
    JOIN company c ON c.companyuser_id = cu.id
    JOIN feedback f ON f.company_id = c.id
GROUP BY cu.id;

但查询结果中feedbacks仅返回一条数据，不符合预期。

解决方案 #

问题核心是未使用聚合函数合并多条反馈数据，需要用JSON_ARRAYAGG将同层级的JSON对象聚合为数组，从最内层的反馈开始，逐层向上聚合到公司、用户层级。

正确SQL语句 #

SELECT JSON_ARRAYAGG(
    JSON_OBJECT(
        'user', cu.name,
        'companies', company_arr
    )
) AS result
FROM companyuser cu
LEFT JOIN (
    SELECT 
        c.companyuser_id,
        JSON_ARRAYAGG(
            JSON_OBJECT(
                'name', c.name,
                'feedbacks', feedback_arr
            )
        ) AS company_arr
    FROM company c
    LEFT JOIN (
        SELECT 
            company_id,
            JSON_ARRAYAGG(
                JSON_OBJECT(
                    'id', f.id,
                    'feedback', f.feedback
                )
            ) AS feedback_arr
        FROM feedback f
        GROUP BY company_id
    ) f ON f.company_id = c.id
    GROUP BY c.companyuser_id
) c ON c.companyuser_id = cu.id;

语句说明 #

1. 最内层子查询：按company_id分组，用JSON_ARRAYAGG将同一家公司的所有反馈生成JSON数组feedback_arr。
2. 中间层子查询：按companyuser_id分组，将每个用户的公司信息及其对应的反馈数组聚合为company_arr。
3. 最外层查询：将所有用户的信息及对应的公司数组聚合，生成最终的嵌套JSON数组。

使用LEFT JOIN可确保即使用户没有公司、公司没有反馈，也能保留对应的空数组结构，避免数据丢失。

内容的提问来源于stack exchange，提问作者Yato