mysql - MySQL 中的 JSON，返回 1(共 3 个)部门，列出所有员工

Question

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

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"]}
]}} ' );

使用相同的查询，我又添加了 2 个部门，每个部门有 3 名员工。 Demo on DB Fiddle .我只想返回 1 个部门并列出所有员工，因此它看起来像这样:

部门名称 |员工
“营销” | "Mary Jones, Tom Robinson, Olivia Johnson"(引号的位置无关紧要)

但我能弄清楚的最接近的是这个查询，它列出了所有部门，每个部门只列出了第一名员工:

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

这是家庭作业 - 初学者类(class)，我已经努力学习，甚至达到了这一点。任何帮助将不胜感激。

Answer 1

这很简单，选择所有名称进行营销

select
    jsondocument->'$.department.deptname' as deptname, 
    jsondocument->'$.department.employee[*].empname' as employees
from department
HAVING deptname = 'Marketing';

deptname    | employees                                       :---------- | :-----------------------------------------------"Marketing" | ["Mary Jones", "Tom Robinson", "Olivia Johnson"]

db<> fiddle here