Kayshav.com
About Developer Oracle 19c Oracle 12c Technology Information Sitemap

JSON - Oracle 19c
-- 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_ARRAY

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

Management Hierarchy

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;

JSON_ARRAYAGG


  Oracle 19c Index   JSON Updates   JSON_OBJECT

  Database Reference   11g Index   12c Index

Oracle registered trademark of Oracle Corporation.

Last Revised On: January 27th, 2021

  5051