Oracle APEX Rest API master detail array

Question

I wrote the query below, and it's working fine in the SQL workshop. I also validated that the JSON format is correct. However, when I create the GET API, it gives an error:

SELECT 'application/json' as content_type, JSON_OBJECT (
         KEY 'departments' VALUE (
           SELECT JSON_ARRAYAGG(
                    JSON_OBJECT (
                      KEY 'department_name' VALUE d.DEPARTMENT_NAME,
                      KEY 'department_no' VALUE d.DEPARTMENT_ID,
                      KEY 'employees' VALUE (
                        SELECT JSON_ARRAYAGG (
                                 JSON_OBJECT(
                                   KEY 'employee_number' VALUE e.EMPLOYEE_ID,
                                   KEY 'employee_name' VALUE e.FIRST_NAME
                                 )
                               )
                        FROM   OEHR_EMPLOYEES e
                        WHERE  e.DEPARTMENT_ID = d.DEPARTMENT_ID
                        
                      )
                    )
                  )
           FROM   OEHR_DEPARTMENTS d
           where d.DEPARTMENT_ID in (10,20,30)
         )
       ) AS departments
FROM   dual;

Answer 1

Remove the semi-colon at the end.