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;

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

评论
  • 纯情小火鸡
    纯情小火鸡 回复

    这很简单,选择所有营销名称

    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

  • pdolor
    pdolor 回复

    If you are running MySQL 8.0, you can use json_table() to unnest the employee array, and then aggregation to put all employee names on one row.

    select 
        d.jsondocument ->> '$.department.deptname' deptname, 
        group_concat(j.empname) employees
    from department d
    cross join json_table(
        d.jsondocument -> '$.department.employee',
        '$[*]'
        columns(
            empid int path '$.empid',
            empname varchar(100) path '$.empname',
            empphone varchar(20) path '$.empphone',
            empemail json path '$.empemail'
        )
    ) j
    group by d.jsondocument ->> '$.department.deptid', deptname
    

    Actually if you only need the empnames, you can shorten the query a little:

    select 
        d.jsondocument ->> '$.department.deptname' deptname, 
        group_concat(j.empname) employees
    from department d
    cross join json_table(
        d.jsondocument -> '$.department.employee',
        '$[*]'
        columns(empname varchar(100) path '$.empname')
    ) j
    group by d.jsondocument ->> '$.department.deptid', deptname
    

    Demo on DB Fiddle:

    部门名称|雇员
    :-------- | :-------------------------------------
    市场营销玛丽·琼斯(Mary Jones),汤姆·罗宾逊(Tom Robinson),奥利维亚·约翰逊(Olivia Johnson)