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)
点赞
评论