MySQL 8.0 可以在 Select 语句中返回 JSON 对象数组吗

是否可以使用MySQL8.0返回json对象数组？例如，我有四个MySQL表:用户、项目、任务和时间.它们被定义为:

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);

然后，我try 了这个查询，得到了以下结果:

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}] |
+------------+--------------+----------------------------------------------------------------------------------------------------+

可以像这样退回一行吗？

------------+--------------+----------------------------------------------------------------------------------------------------+
| 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}] |
+------------+--------------+----------------------------------------------------------------------------------------------------+

这意味着我希望所有time条记录都出现在提供time.task_id == task.task_id小时字段的数组中.我还查看了JSON_OBJECTAGG和JSON_ARRAYAGG，但它们似乎不是我需要的.