https://www.youtube.com/watch?v=BOy3pFFbJU0

DROP TABLE IF EXISTS Employee;

 -- Create the Employee table
CREATE TABLE Employee (
    EmployeeID INT PRIMARY KEY,
    FirstName VARCHAR(50),
    LastName VARCHAR(50), 
    Salary DECIMAL(10, 2),
    HireDate DATE,
    IsActive INT
);

-- Insert data into the Employee table
INSERT INTO Employee (EmployeeID, FirstName, LastName , Salary, HireDate, IsActive)
VALUES
(1, 'John', 'Doe' , 70000.00, '2021-03-15', 1),
(2, 'Jane', 'Smith' , 65000.00, '2019-07-01', 1),
(3, 'Michael', 'Brown' , 80000.00, '2018-08-20', 0),
(4, 'Emily', 'Davis' , 72000.00, '2020-01-10', 1),
(5, 'Robert', 'Miller' , 60000.00, '2022-05-25', 0),
(6, 'Linda', 'Wilson' , 90000.00, '2017-11-30', 1);

DROP TABLE IF EXISTS EmployeeDepartments;
-- Create the EmployeeDepartments table
CREATE TABLE EmployeeDepartments (
    EmployeeID INT,
    Department VARCHAR(50) 
);

-- Insert data into the EmployeeDepartments table
INSERT INTO EmployeeDepartments (EmployeeID, Department)
VALUES
(1, 'IT'),
(1, 'Security'),
(2, 'HR'),
(3, 'Finance'),
(3, 'Compliance'),
(4, 'IT'),
(4, 'Support'),
(5, 'Marketing'),
(5, 'Sales'),
(6, 'Finance'),
(6, 'Auditing');

JSON_OBJECT
JSON_ARRAYAGG

SELECT 
    JSON_OBJECT(
        'Employee.ID', ee.employeeid,
        'Employee.Fullname', CONCAT(firstname, ' ', lastname),
        'Employee.Department', JSON_ARRAYAGG(ed.Department)
    ) AS EmployeeJSON
FROM 
    employees ee
LEFT JOIN 
    employeedepartments ed ON ee.employeeid = ed.employeeid AND IsActive = 0
GROUP BY 
    ee.employeeid, firstname, lastname;