MySQL 8.0能否在SELECT语句中返回聚合后的JSON对象数组？

我需要实现将同一任务下的所有工时记录聚合到JSON数组中，最终返回单条项目结果行。现有四张表user、project、task、time，表定义如下：

CREATE TABLE IF NOT EXISTS `user` (
  `user_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `user_name` varchar(100) NOT NULL,
  PRIMARY KEY (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `project` (
  `project_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `project_name` varchar(100) NOT NULL,
  PRIMARY KEY (`project_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `task` (
  `task_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `task_name` varchar(100) NOT NULL,
  `project_id` int(11) unsigned NOT NULL,
  PRIMARY KEY (`task_id`),
  CONSTRAINT `task_ibfk_1` FOREIGN KEY (`project_id`) REFERENCES `project` (`project_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `time` (
  `time_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `work_done` varchar(100) NULL,
  `task_id` int(11) unsigned NOT NULL,
  `user_id` int(11) unsigned NOT NULL,
  `hours` decimal(10,2) NOT NULL,
  PRIMARY KEY (`time_id`),
  CONSTRAINT `time_ibfk_1` FOREIGN KEY (`task_id`) REFERENCES `task` (`task_id`) ON DELETE CASCADE,
  CONSTRAINT `time_ibfk_2` FOREIGN KEY (`user_id`) REFERENCES `user` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

插入测试数据：

INSERT INTO user (`user_id`, `user_name`) VALUES (1, 'user 1');
INSERT INTO project (`project_id`, `project_name`) VALUES (1, 'Project 1');
INSERT INTO task (`task_id`, `task_name`, `project_id`) VALUES (1, 'Task 1', 1);
INSERT INTO time (`task_id`, `user_id`, `hours`) VALUES (1, 1, 1), (1,1,2.5);

当前查询会返回两行结果，每个time记录单独占一行：

SELECT project.project_id, project_name,
JSON_ARRAY(
  JSON_OBJECT(
    'project_id', task.project_id, 'task_name', task.task_name,
    'hours', JSON_ARRAY(JSON_OBJECT('task_id', time.task_id, 'user_id', time.user_id, 'hours', hours))
  )
) AS tasks
FROM project
INNER JOIN task ON task.project_id = project.project_id
INNER JOIN time ON time.task_id = task.task_id

查询结果：

+------------+--------------+----------------------------------------------------------------------------------------------------+
| project_id | project_name | tasks                                                                                              |
+------------+--------------+----------------------------------------------------------------------------------------------------+
|          1 | Project 1    | [{"hours": [{"hours": 1.00, "task_id": 1, "user_id": 1}], "task_name": "Task 1", "project_id": 1}] |
|          1 | Project 1    | [{"hours": [{"hours": 2.50, "task_id": 1, "user_id": 1}], "task_name": "Task 1", "project_id": 1}] |
+------------+--------------+----------------------------------------------------------------------------------------------------+

期望得到单条结果行，将同一task下的所有time记录聚合到hours数组中：

+------------+--------------+----------------------------------------------------------------------------------------------------+
| project_id | project_name | tasks                                                                                              |
+------------+--------------+----------------------------------------------------------------------------------------------------+
|          1 | Project 1    | [{"hours": [{"hours": 1.00, "task_id": 1, "user_id": 1},{"hours": 2.50, "task_id": 1, "user_id": 1}], "task_name": "Task 1", "project_id": 1}] |
+------------+--------------+----------------------------------------------------------------------------------------------------+

解决方案 #

可以通过嵌套聚合实现需求，先用JSON_ARRAYAGG聚合每个任务对应的工时记录为数组，再将任务聚合为项目的tasks数组。有两种实现方式：

方式1：子查询聚合工时 #

SELECT 
  p.project_id,
  p.project_name,
  JSON_ARRAYAGG(
    JSON_OBJECT(
      'project_id', t.project_id,
      'task_name', t.task_name,
      'hours', (
        SELECT JSON_ARRAYAGG(
          JSON_OBJECT(
            'task_id', tm.task_id,
            'user_id', tm.user_id,
            'hours', tm.hours
          )
        ) FROM time tm WHERE tm.task_id = t.task_id
      )
    )
  ) AS tasks
FROM project p
INNER JOIN task t ON t.project_id = p.project_id
GROUP BY p.project_id, p.project_name;

方式2：JOIN预聚合工时（避免子查询） #

SELECT 
  p.project_id,
  p.project_name,
  JSON_ARRAYAGG(
    JSON_OBJECT(
      'project_id', t.project_id,
      'task_name', t.task_name,
      'hours', tm.hours_array
    )
  ) AS tasks
FROM project p
INNER JOIN task t ON t.project_id = p.project_id
INNER JOIN (
  SELECT 
    task_id,
    JSON_ARRAYAGG(
      JSON_OBJECT(
        'task_id', task_id,
        'user_id', user_id,
        'hours', hours
      )
    ) AS hours_array
  FROM time
  GROUP BY task_id
) tm ON tm.task_id = t.task_id
GROUP BY p.project_id, p.project_name;

执行任意一种查询后，都会得到期望的单条结果，将同一任务下的所有工时记录聚合到hours数组中。

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