JSON in MySQL, return 1 (out of 3) departments, listing all employees

user_3314420 0
Viewed 67

In the department table, I have two fields:

documentid, which is INT
jsondocument which is JSON

I executed the following query:

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:

deptname | employees
"Marketing" | "Mary Jones, Tom Robinson, Olivia Johnson" (location of quotations marks doesn't matter)

But the closest I've been able to figure out is this query, which lists all the departments and only the 1st employee in each:

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

It's homework -- a beginner class and I've studied hard to even get to this point. Any help would be appreciated.

group-by mysql arrays sql json
2 Answers

That is quite simple, to select all Names for Marketing

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

user_51935360

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:

deptname  | employees                             
:-------- | :-------------------------------------
Marketing | Mary Jones,Tom Robinson,Olivia Johnson
user_106767160
Related