-- JSON_ARRAY

SELECT
 JSON_OBJECT('Title'  VALUE  title,
             'Salary' VALUE  JSON_ARRAY(smin, smax))
  as  JSON_Output
FROM (
  SELECT 'CEO' title,  850000 smin,  1000000  smax  FROM DUAL UNION 
  SELECT 'CFO' title,  500000 smin,   800000  smax  FROM DUAL UNION
  SELECT 'VP'  title,  400000 smin,   450000  smax  FROM DUAL UNION
  SELECT 'DIR' title,  250000 smin,   300000  smax  FROM DUAL UNION
  SELECT 'MGR' title,  150000 smin,   225000  smax  FROM DUAL
)
;



-- JSON_ARRAYAGG
-- Analyze Management Hierarchy using JSON function JSON_ARRAYAGG



SELECT
 JSON_OBJECT('MGR-ID'      VALUE  q_tab.mgr_no,
             'Emp-Count'   VALUE  COUNT(q_tab.emp_no),
             'Emp_IDs'     VALUE  JSON_ARRAYAGG(q_tab.emp_no
                                    ORDER BY q_tab.emp_no)
            ) JSON_Manager_Emp_op
FROM
(SELECT  1 no, 1001  emp_no,  102  mgr_no FROM DUAL UNION
 SELECT  2 no, 1002  emp_no,  102  mgr_no FROM DUAL UNION
 SELECT  3 no, 1003  emp_no,  102  mgr_no FROM DUAL UNION
 SELECT  4 no, 1004  emp_no, 1001  mgr_no FROM DUAL UNION
 SELECT  5 no, 1005  emp_no, 1001  mgr_no FROM DUAL UNION
 SELECT  6 no, 1006  emp_no, 1002  mgr_no FROM DUAL UNION
 SELECT  7 no, 1007  emp_no, 1002  mgr_no FROM DUAL UNION
 SELECT  8 no, 1008  emp_no, 1003  mgr_no FROM DUAL UNION
 SELECT  9 no, 1009  emp_no, 1003  mgr_no FROM DUAL UNION
 SELECT 10 no,  101  emp_no,  100  mgr_no FROM DUAL UNION
 SELECT 11 no,  102  emp_no,  101  mgr_no FROM DUAL
) q_tab
GROUP BY q_tab.mgr_no;