Example 23-1 Declaring an Input Value To Be JSON

This example specifies FORMAT JSON for SQL string values 'true' and 'false', in order that the JSON Boolean values true and false are used. Without specifying FORMAT JSON, the values of field hasCommission would be the JSON string values "true" and "false", not the JSON Boolean values true and false.

SELECT json_object('name'          VALUE first_name || ' ' || last_name,
                   'hasCommission' VALUE
                     CASE WHEN commission_pct IS NULL THEN 'false'
                                                      ELSE 'true'
                     END FORMAT JSON)
  FROM employees WHERE first_name LIKE 'W%';

JSON_OBJECT('NAME'ISFIRST_NAME||''||LAST_NAME,'
-----------------------------------------------
{"name":"William Gietz","hasCommission":false}
{"name":"William Smith","hasCommission":true}
{"name":"Winston Taylor","hasCommission":false}

Example 23-2 Using Name–Value Pairs with JSON_OBJECT

This example constructs a JSON object for each employee of table hr.employees (from standard database schema HR) whose salary is greater than 15000.

It passes explicit name–value pairs to specify the members of the JSON object. The object includes, as the value of its field contactInfo, an object with fields mail and phone.

The use of RETURNING JSON here specifies that the JSON data is returned as JSON data type, not the default return type, VARCHAR2(4000).

SELECT json_object('id'          : employee_id,
                   'name'        : first_name || ' ' || last_name,
                   'contactInfo' : json_object('mail'  : email,
                                               'phone' : phone_number),
                   'hireDate'    : hire_date,
                   'pay'         : salary
                   RETURNING JSON) 
  FROM hr.employees
  WHERE salary > 15000;

-- The query returns rows such as this (pretty-printed here for clarity):

{"id"          : 101,
 "name"        : "Neena Kochhar",
 "contactInfo" : {"mail"  : "NKOCHHAR",
                  "phone" : "515.123.4568"},
 "hireDate"    : "21-SEP-05",
 "pay"         : 17000}

"contactInfo" : '{"mail":' || email ', "phone":' || phone_number || '}'
FORMAT JSON,

Because the return type of the JSON data is JSON, this is an alternative syntax for the same query:

SELECT JSON { 'id'          : employee_id,
              'name'        : first_name || ' ' || last_name,
              'contactInfo' : JSON { 'mail'  : email,
                                     'phone' : phone_number }
              'hireDate'    : hire_date,
              'pay'         : salary } 
  FROM hr.employees
  WHERE salary > 15000;

Example 23-3 Using Column Names with JSON_OBJECT

This example constructs a JSON object for the employee whose employee_id is 101. The fields produced are named after the columns, but case-sensitively.

SELECT json_object(last_name,
                   'contactInfo' : json_object(email, phone_number),
                   hire_date,
                   salary,
                   RETURNING JSON)
  FROM hr.employees
  WHERE employee_id = 101;

-- The query returns rows such as this (pretty-printed here for clarity):

{"last_name"   : "Kochhar",
 "contactInfo" : {"email"        : "NKOCHHAR",
                  "phone_number" : "515.123.4568"},
 "hire-date"   : "21-SEP-05",
 "salary"      : 17000}

Because the return type of the JSON data is JSON, this is an alternative syntax for the same query:

SELECT JSON { last_name,
              'contactInfo' : JSON { email, phone_number },
              hire_date,
              salary}
  FROM hr.employees
  WHERE employee_id = 101;

Example 23-4 Using a Wildcard (*) with JSON_OBJECT

This example constructs a JSON object for each employee whose salary is greater than 15000. Each column of table employees is used to construct one object member, whose field name is the (uppercase) column name. Note that a SQL NULL value results in a JSON field value of null.

SELECT json_object(* RETURNING JSON)
  FROM hr.employees
  WHERE salary > 15000;

-- The query returns rows such as this (pretty-printed here for clarity):

JSON_OBJECT(*)
--------------
{"EMPLOYEE_ID":100,
 "FIRST_NAME":"Steven",
 "LAST_NAME":"King",
 "EMAIL":"SKING",
 "PHONE_NUMBER":"515.123.4567",
 "HIRE_DATE":"2003-06-17T00:00:00",
 "JOB_ID":"AD_PRES",
 "SALARY":24000,
 "COMMISSION_PCT":null,
 "MANAGER_ID":null,
 "DEPARTMENT_ID":90}

{"EMPLOYEE_ID":101,
 "FIRST_NAME":"Neena",
 "LAST_NAME":"Kochhar",
 "EMAIL":"NKOCHHAR",
 "PHONE_NUMBER":"515.123.4568",
 "HIRE_DATE":"2005-09-21T00:00:00",
 "JOB_ID":"AD_VP",
 "SALARY":17000,
 "COMMISSION_PCT":null,
 "MANAGER_ID":100,
 "DEPARTMENT_ID":90}

{"EMPLOYEE_ID":102,
 "FIRST_NAME":"Lex",
 "LAST_NAME":"De Haan",
 "EMAIL":"LDEHAAN",
 "PHONE_NUMBER":"515.123.4569",
 "HIRE_DATE":"2001-01-13T00:00:00",
 "JOB_ID":"AD_VP",
 "SALARY":17000,
 "COMMISSION_PCT":null,
 "MANAGER_ID":100,
 "DEPARTMENT_ID":90}

Because the return type of the JSON data is JSON, this is an alternative syntax for the same query:

SELECT JSON { * }
  FROM hr.employees
  WHERE salary > 15000;

Example 23-5 Using JSON_OBJECT With ABSENT ON NULL

This example queries table hr.locations from standard database schema HR to create JSON objects with fields city and province.

The default NULL-handling behavior for json_object is NULL ON NULL.

In order to prevent the creation of a field with a null JSON value, this example uses ABSENT ON NULL. The NULL SQL value for column state_province when column city has value 'Singapore' means that no province field is created for that location.

SELECT JSON_OBJECT('city'     VALUE city,
                   'province' : state_province ABSENT ON NULL) 
  FROM hr.locations
  WHERE city LIKE 'S%';

JSON_OBJECT('CITY'ISCITY,'PROVINCE'ISSTATE_PROVINCEABSENTONNULL)
----------------------------------------------------------------
{"city":"Southlake","province":"Texas"}
{"city":"South San Francisco","province":"California"}
{"city":"South Brunswick","province":"New Jersey"}
{"city":"Seattle","province":"Washington"}
{"city":"Sydney","province":"New South Wales"}
{"city":"Singapore"}
{"city":"Stretford","province":"Manchester"}
{"city":"Sao Paulo","province":"Sao Paulo"}

Because there is no RETURNING clause in this example, the JSON data is returned as VARCHAR2(4000), the default. If RETURNING JSON were used then you could use this alternative syntax for the query:

SELECT JSON {'city'     VALUE city,
                   'province' : state_province ABSENT ON NULL} 
  FROM hr.locations
  WHERE city LIKE 'S%';

Example 23-6 Using a User-Defined Object-Type Instance with JSON_OBJECT

This example creates table po_ship with column shipping of object type shipping_t. (It uses SQL/JSON function json_value to construct the shipping_t instances from JSON data — see Example 18-3.)

It then uses json_object to generate JSON objects from the SQL object-type instances in column po_ship.shipping, returning them as JSON data type instances.

(The query output is shown pretty-printed here, for clarity.)

CREATE TABLE po_ship
  AS SELECT json_value(po_document, '$.ShippingInstructions'
                       RETURNING shipping_t)
  shipping
  FROM j_purchaseorder;

DESCRIBE po_ship;

Name      Null?   Type
--------- ------- ----------
SHIPPING          SHIPPING_T

SELECT json_object(shipping RETURNING JSON)
  FROM po_ship;

JSON_OBJECT(SHIPPING)
---------------------
{"NAME":"Alexis Bull",
 "ADDRESS":{"STREET":"200 Sporting Green",
            "CITY":"South San Francisco"}}
{"NAME":"Sarah Bell",
 "ADDRESS":{"STREET":"200 Sporting Green",
            "CITY":"South San Francisco"}}

Because the return type from json_object is JSON, this is an alternative syntax for the same query:

SELECT JSON {shipping} FROM po_ship;

Example 23-7 Using JSON_ARRAY to Construct a JSON Array

This example constructs a JSON object for each employee job in database table hr.jobs (from standard database schema HR). The fields of the objects are the job title and salary range. The salary range (field salaryRange) is an array of two numeric values, the minimum and maximum salaries for the job. These values are taken from SQL columns min_salary and max_salary.

The use of RETURNING JSON here specifies that the JSON data is returned as JSON data type, not the default return type, VARCHAR2(4000).

SELECT json_object('title'       VALUE job_title, 
                   'salaryRange' VALUE json_array(min_salary, max_salary)
                   RETURNING JSON)
  FROM jobs;

JSON_OBJECT('TITLE'ISJOB_TITLE,'SALARYRANGE'ISJSON_ARRAY(MIN_SALARY,
--------------------------------------------------------------------
{"title":"President","salaryRange":[20080,40000]}
{"title":"Administration Vice President","salaryRange":[15000,30000]}
{"title":"Administration Assistant","salaryRange":[3000,6000]}
{"title":"Finance Manager","salaryRange":[8200,16000]}
{"title":"Accountant","salaryRange":[4200,9000]}
{"title":"Accounting Manager","salaryRange":[8200,16000]}
{"title":"Public Accountant","salaryRange":[4200,9000]}
{"title":"Sales Manager","salaryRange":[10000,20080]}
{"title":"Sales Representative","salaryRange":[6000,12008]}
{"title":"Purchasing Manager","salaryRange":[8000,15000]}
{"title":"Purchasing Clerk","salaryRange":[2500,5500]}
{"title":"Stock Manager","salaryRange":[5500,8500]}
{"title":"Stock Clerk","salaryRange":[2008,5000]}
{"title":"Shipping Clerk","salaryRange":[2500,5500]}
{"title":"Programmer","salaryRange":[4000,10000]}
{"title":"Marketing Manager","salaryRange":[9000,15000]}
{"title":"Marketing Representative","salaryRange":[4000,9000]}
{"title":"Human Resources Representative","salaryRange":[4000,9000]}
{"title":"Public Relations Representative","salaryRange":[4500,10500]}

Because the return type of the JSON data is JSON, this is an alternative syntax for the same query:

SELECT JSON { 'title'       VALUE job_title, 
              'salaryRange' VALUE JSON [ min_salary, max_salary ] }
  FROM jobs;

Example 23-8 Using JSON_OBJECTAGG to Construct a JSON Object

This example constructs a single JSON object from table hr.departments (from standard database schema HR) using field names taken from column department_name and field values taken from column department_id.

Just as for SQL/JSON condition is json, you can use keywords STRICT and WITH UNIQUE KEYS with functions json_object and json_objectagg. The behavior for each is the same as for is json.

SELECT json_objectagg(department_name VALUE department_id)
  FROM departments;

-- The returned object is pretty-printed here for clarity.
-- The order of the object members is arbitrary.

JSON_OBJECTAGG(DEPARTMENT_NAMEISDEPARTMENT_ID)
----------------------------------------------
{"Administration":        10,
 "Marketing":             20,
 "Purchasing":            30,
 "Human Resources":       40,
 "Shipping":              50,
 "IT":                    60,
 "Public Relations":      70,
 "Sales":                 80,
 "Executive":             90,
 "Finance":              100,
 "Accounting":           110,
 "Treasury":             120,
 "Corporate Tax":        130,
 "Control And Credit":   140,
 "Shareholder Services": 150,
 "Benefits":             160,
 "Manufacturing":        170,
 "Construction":         180,
 "Contracting":          190,
 "Operations":           200,
 "IT Support":           210,
 "NOC":                  220,
 "IT Helpdesk":          230,
 "Government Sales":     240,
 "Retail Sales":         250,
 "Recruiting":           260,
 "Payroll":              270}

Example 23-9 Using JSON_ARRAYAGG to Construct a JSON Array

This example constructs a JSON object for each employee of table hr.employees (from standard database schema HR) who is a manager in charge of at least six employees. The objects have fields for the manager id number, manager name, number of employees reporting to the manager, and id numbers of those employees.

The order of the employee id numbers in the array is determined by the ORDER BY clause for json_arrayagg. The default direction for ORDER BY is ASC (ascending). The array elements, which are numeric, are in ascending numerical order.

SELECT json_object('id'         VALUE mgr.employee_id, 
                   'manager'    VALUE (mgr.first_name || ' '|| mgr.last_name),
                   'numReports' VALUE count(rpt.employee_id),
                   'reports'    VALUE json_arrayagg(rpt.employee_id
                                                    ORDER BY rpt.employee_id))
  FROM  employees mgr, employees rpt
  WHERE mgr.employee_id = rpt.manager_id
  GROUP BY mgr.employee_id, mgr.last_name, mgr.first_name
  HAVING count(rpt.employee_id) > 6;

-- The returned object is pretty-printed here for clarity.

JSON_OBJECT('ID'ISMGR.EMPLOYEE_ID,'MANAGER'VALUE(MGR.FIRST_NAME||''||MGR.LAST_NAME)
-----------------------------------------------------------------------------------
{"id":         100,
 "manager":    "Steven King",
 "numReports": 14,
 "reports":    [101,102,114,120,121,122,123,124,145,146,147,148,149,201]}

{"id":         120,
 "manager":    "Matthew Weiss",
 "numReports": 8,
 "reports":    [125,126,127,128,180,181,182,183]}

{"id":         121,
 "manager":    "Adam Fripp",
 "numReports": 8,
 "reports":    [129,130,131,132,184,185,186,187]}

{"id":         122,
 "manager":    "Payam Kaufling",
 "numReports": 8,
 "reports":    [133,134,135,136,188,189,190,191]}

{"id":         123,
 "manager":    "Shanta Vollman",
 "numReports": 8,
 "reports":    [137,138,139,140,192,193,194,195]}

{"id":         124,
 "manager":    "Kevin Mourgos",
 "numReports": 8,
 "reports":    [141,142,143,144,196,197,198,199]}