MySQL中的JSON，返回1（共3个）部门，列出所有员工

由 发布于

在部门表中，我有两个字段：

documentid, which is INT
jsondocument which is JSON

我执行了以下查询：

INSERT INTO department VALUES
(1,'{"department":{
"deptid":"d1",
"deptname":"Marketing",
"deptroom":"Room 7",
"deptphone":["465-8541","465-8542","465-8543"],
"employee":[{
"empid":"e1",
"empname":"Mary Jones",
"empphone":"465-8544",
"empemail":["mjones@gmail.com","mjones@company.com"]},
{
"empid":"e2",
"empname":"Tom Robinson",
"empphone":"465-8545",
"empemail":["trobinson@gmail.com","trobinson@company.com"]},
{
"empid":"e3",
"empname":"Olivia Johnson",
"empphone":"465-8546",
"empemail":["ojohnson@gmail.com","ojohnson@company.com"]}
]}} ' );

Using the same query, I added 2 more departments with 3 employees each. Demo on DB Fiddle. I want to return only 1 department and list all the employees so it would look like this:

部门名称|雇员 “营销” | “玛丽·琼斯，汤姆·罗宾逊，奥利维亚·约翰逊”（引号的位置无关紧要）

但是我能找出的最接近的查询是此查询，它列出了所有部门，每个部门中只有第一名员工：

select
    jsondocument->'$.department.deptname' as deptname, 
    jsondocument->'$.department.employee[0].empname' as employees
from department;

这是家庭作业-初学者课程，为了达到这一目标，我努力学习。任何帮助，将不胜感激。