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

Oracle JSON_OBJECT Functions
-- Oracle 19c: JSON_OBJECT 

SELECT
 JSON_OBJECT('Name'  VALUE  last_name||', '||first_name
             FORMAT JSON)  as  JSON_Output
FROM (
  SELECT 'FN1' first_name, 'LN1' last_name FROM DUAL UNION
  SELECT 'FN2' first_name, 'LN2' last_name FROM DUAL
)
;

-- Exclude missing data in JSON output

SELECT
 JSON_OBJECT('Name'  VALUE  last_name||', '||first_name
             ABSENT ON NULL)  as  JSON_Output
FROM (
  SELECT 'FN1' first_name,  'LN1'  last_name FROM DUAL UNION
  SELECT 'FN2' first_name,  'LN2'  last_name FROM DUAL UNION
  SELECT 'FN2' first_name,  NULL   last_name FROM DUAL
)
;

JSON_OBJECTAGG

-- Oracle 19c: JSON_OBJECTAGG 

SELECT
 JSON_OBJECTAGG(title  VALUE  smin||', '||smax)  json_objectagg_op
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_OBJECTAGG



  Oracle 19c Index   JSON Updates   JSON_ARRAY

  Database Reference   11g Index   12c Index

Oracle registered trademark of Oracle Corporation.

Last Revised On: January 27th, 2021

  5054