SQL/JSON Function JSON

25.4 SQL/JSON Function JSON_ARRAY

SQL/JSON function json_array constructs a JSON array from the results of evaluating its argument SQL expressions.

In the simplest case, the evaluated arguments you provide to json_array are SQL values that produce JSON values as the JSON array elements. The resulting array has an element for each argument you provide (except when an argument expression evaluates to SQL NULL and the ABSENT ON NULL clause applies). Array element order is the same as the argument order.

There are several kinds of SQL value that you can use as an argument to json_array, including SQL scalar, collection instance, and user-defined object-type instance. Alternatively, the argument can be a (sub)query expression, in which case the array elements are the values returned by the query, in order (or according to ORDER BY, if present).

Example 25-8 Using JSON_ARRAY with Value Arguments 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 [ min_salary, max_salary ] }
  FROM jobs;

Example 25-9 Using JSON_ARRAY with a Query Argument to Construct a JSON Array

This query passes a subquery as argument to function json_array. The subquery invokes function json_object, which produces object values as the array elements. The array elements are ordered by the values of their field sal, by virtue of keywords ORDER BY, which sorts the subquery values by column 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_array(SELECT json_object('id'   : employee_id,
                                     'name' : last_name,
                                     'sal'  : salary)
                    RETURNING JSON
                    FROM employees
                    WHERE salary > 12000
                    ORDER BY salary) by_salary;

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

SELECT JSON [ SELECT JSON {'id'   : employee_id,
                           'name' : last_name,
                           'sal'  : salary}
                FROM employees
                WHERE salary > 12000
                ORDER BY salary ] by_salary;