Generation of JSON Data Using SQL

23 Generation of JSON Data Using SQL

You can use SQL to generate JSON objects and arrays from non-JSON data in the database. For that, use either constructor JSON or SQL/JSON functions json_object, json_array, json_objectagg, and json_arrayagg.

Overview of JSON Generation
An overview is presented of JSON data generation: best practices, the SQL/JSON generation functions, a simple JSON constructor syntax, handling of input SQL values, and resulting generated data.
Handling of Input Values For SQL/JSON Generation Functions
The SQL/JSON generation functions take SQL values as input and return a JSON object or array. The input values are used to produce JSON object field–value pairs or JSON array elements. How the input values are used depends on their SQL data type.
SQL/JSON Function JSON_OBJECT
SQL/JSON function json_object constructs JSON objects from the results of evaluating its argument SQL expressions.
SQL/JSON Function JSON_ARRAY
SQL/JSON function json_array constructs a JSON array from the results of evaluating its argument SQL expressions.
SQL/JSON Function JSON_OBJECTAGG
SQL/JSON function json_objectagg constructs a JSON object by aggregating information from multiple rows of a grouped SQL query as the object members.
SQL/JSON Function JSON_ARRAYAGG
SQL/JSON function json_arrayagg constructs a JSON array by aggregating information from multiple rows of a grouped SQL query as the array elements. The order of array elements reflects the query result order, by default, but you can use the ORDER BY clause to impose array element order.

Parent topic: Generation of JSON Data

23.1 Overview of JSON Generation

An overview is presented of JSON data generation: best practices, the SQL/JSON generation functions, a simple JSON constructor syntax, handling of input SQL values, and resulting generated data.

The best way to generate JSON data from non-JSON database data is to use SQL. The standard SQL/JSON functions, json_object, json_array, json_objectagg, and json_arrayagg are designed specifically for this. If the generated data is of JSON type then a handy alternative is to use the JSON data type constructor function, JSON.

Both make it easy to construct JSON data directly from a SQL query. They allow non-JSON data to be represented as JSON objects and JSON arrays. You can generate complex, hierarchical JSON documents by nesting calls to the generation functions or constructor JSON. Nested subqueries can generate JSON collections that represent one-to-many relationships.^Foot 1

The Best Way to Construct JSON Data from Non-JSON Data

Alternatives to using the SQL/JSON generation functions are generally error prone or inefficient.

Using string concatenation to generate JSON documents is error prone. In particular, there are a number of complex rules that must be respected concerning when and how to escape special characters, such as double quotation marks ("). It is easy to overlook or misunderstand these rules, which can result in generating incorrect JSON data.
Reading non-JSON result sets from the database and using client-side application code to generate JSON data is typically quite inefficient, particularly due to network overhead. When representing one-to-many relationships as JSON data, multiple SELECT operations are often required, to collect all of the non-JSON data needed. If the documents to be generated represent multiple levels of one-to-many relationships then this technique can be quite costly.

The SQL/JSON generation functions and constructor JSON do not suffer from such problems; they are designed for the job of constructing JSON data from non-JSON database data.

They always construct well-formed JSON documents.
By using SQL subqueries with the functions, you can generate an entire set of JSON documents using a single SQL statement, which allows the generation operation to be optimized.
Because only the generated documents are returned to a client, network overhead is minimized: there is at most one round trip per document generated.

The SQL/JSON Generation Functions

Functions json_object and json_array construct a JSON object or array, respectively. In the simplest case, json_object takes SQL name–value pairs as arguments, and json_array takes SQL values as arguments.
Functions json_objectagg, and json_arrayagg are aggregate SQL functions. They transform information that is contained in the rows of a grouped SQL query into JSON objects and arrays, respectively. Evaluation of the arguments determines the number of object members and array elements, respectively; that is, the size of the result reflects the current queried data.

For json_objectagg and json_arrayagg, the order of object members and array elements, respectively, is unspecified. For json_arrayagg, you can use an ORDER BY clause within the json_arrayagg invocation to control the array element order.

Result Returned by SQL/JSON Generation Functions

By default, the generated JSON data is returned from a generation function as a SQL VARCHAR2(4000) value. You can use the optional RETURNING clause to specify a different VARCHAR2 size or to specify a JSON, CLOB or BLOB return value instead. When BLOB is the return type, the character set is AL32UTF8.

Unless the return type is JSON, the JSON values produced from the input SQL values are serialized to textual JSON. This serialization has the same effect as Oracle SQL function json_serialize.

Handling of Input Values For SQL/JSON Generation Functions

The SQL/JSON generation functions take SQL values as input and, from them, produce JSON values inside the JSON object or array that is returned. How the input values produce the JSON values used in the output depends on their SQL data type.

Optional Behavior For SQL/JSON Generation Functions

You can optionally specify a SQL NULL-handling clause, a RETURNING clause, and keywords STRICT and WITH UNIQUE KEYS.

NULL-handling clause — Determines how a SQL NULL value resulting from input evaluation is handled.
- NULL ON NULL — An input SQL NULL value is converted to JSON null for output. This is the default behavior for json_object and json_objectagg.
- ABSENT ON NULL — An input SQL NULL value results in no corresponding output. This is the default behavior for json_array and json_arrayagg.
RETURNING clause — The SQL data type used for the function return value. The return type can be any of the SQL types that support JSON data: JSON, VARCHAR2, CLOB, or BLOB. The default return type (no RETURNING clause) is VARCHAR2(4000).
STRICT keyword — If present, the returned JSON data is checked to be sure it is well-formed. If STRICT is present and the returned data is not well-formed then an error is raised.

Note:

In general, you need not specify STRICT when generating data of JSON data type, and doing so can introduce a small performance penalty.

When an input and the returned data are both of JSON type, if you do not specify STRICT then that input is used as is in the returned data; it is not checked for strict well-formedness.

You might want to use STRICT when returning JSON type data if (1) the input data is also of JSON type and (2) you suspect that it is not completely strict. That could be the case, for example, if a client application created the input data and it did not ensure that each JSON string is represented by a valid UTF-8 sequence of bytes.
WITH UNIQUE KEYS keywords (available only for json_object and json_objectagg) — If present, the returned JSON object is checked to be sure there are no duplicate field names. If there are duplicates, an error is raised.

If absent (or if WITHOUT UNIQUE KEYS is present) then no check for unique fields is performed. In that case:
- If the return data type is JSON then only one field of a set of duplicates is used, and which is used is undefined.
- If the return data type is not JSON then all fields are used, including any duplicates.

JSON Data Type Constructor

You can use constructor JSON with a special syntax as an alternative to using json_object and json_array when generating data of data type JSON. (You can use constructor JSON and JSON type only if database initialization parameter compatible is at least 20. Otherwise an error is raised.)

The only difference in behavior is that the return data type when you use the constructor is always JSON (there is no RETURNING clause for the constructor).

When employed as an alternative syntax for json_object or json_array, you follow constructor JSON directly with braces ({}) and brackets ([]), respectively, for object and array generation, instead of the usual parentheses (()).

JSON { … } has the same effect as JSON(json_object( … )), which has the same effect as json_object( … RETURNING JSON).
JSON [ … ] has the same effect as JSON(json_array( … )), which has the same effect as json_array( … RETURNING JSON).

All of the behavior and syntax possibilities that json_object and json_array offer when they are used with RETURNING JSON are also available when you use constructor JSON with the special syntax. See, for example, Example 23-2, Example 23-3, Example 23-4, Example 23-5, and Example 23-6.

JSON {…} and JSON […] provide alternative syntax only for json_object and json_array, not for the aggregate generation functions, json_objectagg and json_arrayagg. But you can of course use constructor JSON (without the special syntax) on the result of an explicit call to json_objectagg or json_arrayagg. For example, these two queries are equivalent:

SELECT JSON(json_objectagg(department_name VALUE department_id))
  FROM departments;


SELECT json_objectagg(department_name VALUE department_id
                      RETURNING JSON)
  FROM departments;

Related Topics

See Also:

Oracle Database SQL Language Reference in Oracle Database SQL Language Reference
Oracle Database SQL Language Reference in Oracle Database SQL Language Reference
Oracle Database SQL Language Reference in Oracle Database SQL Language Reference
Oracle Database SQL Language Reference in Oracle Database SQL Language Reference
JSON Type Constructor in Oracle Database SQL Language Reference

Parent topic: Generation of JSON Data Using SQL

23.2 Handling of Input Values For SQL/JSON Generation Functions

The SQL/JSON generation functions take SQL values as input and return a JSON object or array. The input values are used to produce JSON object field–value pairs or JSON array elements. How the input values are used depends on their SQL data type.

The returned JSON object or array is of a SQL data type that supports JSON data: JSON, VARCHAR2, CLOB, or BLOB. The default return type is VARCHAR2(4000). In all cases, the return value is known by the database to contain well-formed JSON data.

Unless it is of JSON data type, an input can optionally be followed by keywords FORMAT JSON, which declares that the value is to be considered as already representing JSON data (you vouch for it), so it is interpreted (parsed) as JSON data. For example, if the input is '{}' then you might want it to produce an empty JSON object, {}, and not a JSON string, "{}". Example 23-1 illustrates the use of FORMAT JSON to cause input SQL string "true" to produce the JSON Boolean value true.

Equivalently, if the input type is not JSON then you can apply SQL function treat with keywords AS JSON to it — the effect is the same as using FORMAT JSON.

If the input data is of JSON type then it is used as is. This includes the case where the JSON type constructor is used. (Do not use FORMAT JSON or treat … AS JSON in this case; otherwise, an error is raised.)

In some cases where an input is not of JSON type, and you do not use FORMAT JSON or treat … AS JSON, Oracle nevertheless knows that the result is JSON data. In such cases using FORMAT JSON or treat … AS JSON is not needed and is optional. This is the case, for example, if the input data is the result of using function json_query or one of the JSON generation functions.

If, one way or another, an input is known to be JSON data then it is used essentially as is to construct the result — it need not be processed in any way. This applies regardless of whether the input represents a JSON scalar, object, or array.

If an input is not known to be JSON data, then it produces a JSON value as follows (any other SQL value raises an error):

An instance of a user-defined SQL object type produces a JSON object whose field names are taken from the object attribute names and whose field values are taken from the object attribute values (to which JSON generation is applied recursively).
An instance of a SQL collection type produces a JSON array whose element values are taken from the collection element values (to which JSON generation is applied recursively).
A VARCHAR2, CLOB, or NVARCHAR value is wrapped in double quotation marks ("), and characters are escaped when necessary to conform to the JSON standard for a JSON string. For example, input SQL input '{}' produces the JSON string "{}".
A numeric value produces a JSON numeric value.

If compatible is at least 20 then NUMBER input produces a JSON number value, BINARY_DOUBLE input produces a JSON double value, and BINARY_FLOAT input produces a JSON float value.

If database initialization parameter compatible is less than 20 then the value is a JSON number, regardless of the numeric input type (NUMBER, BINARY_DOUBLE, or BINARY_FLOAT).

The numeric values of positive and negative infinity, and values that are the undefined result of a numeric operation ("not a number" or NaN), cannot be expressed as JSON numbers. They instead produce the JSON strings "Inf", "-Inf", and "Nan", respectively.
A RAW or BLOB value produces a hexadecimal JSON string, with double quotation marks, (").
A time-related value (DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE, TIMESTAMP WITH LOCAL TIME ZONE, INTERVAL YEAR TO MONTH, or INTERVAL DAY TO SECOND) produces a supported ISO 8601 format, and the result is enclosed in double quotation marks (") as a JSON string.
A BOOLEAN PL/SQL value of TRUE or FALSE produces JSON true or false, respectively.
A SQL NULL value produces JSON null, regardless of the NULL data type.

Note:

For input of data types CLOB and BLOB, an empty instance is distinguished from SQL NULL. It produces an empty JSON string (""). But for input of data types VARCHAR2, NVARCHAR2, and RAW, Oracle SQL treats an empty (zero-length) value as NULL, so do not expect such a value to produce a JSON string.

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}

Related Topics

Parent topic: Generation of JSON Data Using SQL

23.3 SQL/JSON Function JSON_OBJECT

SQL/JSON function json_object constructs JSON objects from the results of evaluating its argument SQL expressions.

It can accept any number of arguments, each of which is one of the following:

An explicit field name–value pair. Example: answer : 42.

A name–value pair argument specifies an object member for the generated JSON object (except when the value expression evaluates to SQL NULL and the ABSENT ON NULL clause applies). The name and value are SQL expressions. The name expression must evaluate to a SQL string. The value expression must evaluate to a SQL value that is of JSON data type or that can be rendered as a JSON value. The name and value expressions are separated by keyword VALUE or a colon (:).

Note:

Some client drivers might try to scan query text and identify bind variables before sending the query to the database. In some such cases a colon as name–value separator in json_object might be misinterpreted as introducing a bind variable. You can use keyword VALUE as the separator to avoid this problem ('Name' VALUE Diderot), or you can simply enclose the value part of the pair in parentheses: 'Name':(Diderot).
A relational column name, possibly preceded by a table name or alias, or a view name followed by a dot (.). Example: t1.address.

In this case, for a given row of data, the JSON-object member specified by the column-name argument has the column name as its field name and the column value as the field value.

Regardless of whether it is quoted, the column name you provide is interpreted case-sensitively. For example, if you use Email as a column-name argument then the data in column EMAIL is used to produce object members with field name Email (not EMAIL).
A table name or alias, or a view name, followed by a dot and an asterisk wildcard (.*). Example: t1.*. (The name or alias can also be prefixed by a database schema name, as in myschema.t1.*.)

In this case, all columns of the table or view are used as input. Each is handled as if it were named explicitly. In particular, the column names are interpreted case-sensitively.

Alternatively, json_object accepts a single argument that is one of the following:

An instance of a user-defined SQL object-type. Example: json_object(my_sql_object_42).

In this case, the resulting JSON-object field names are taken from the SQL object attribute names, and their values are taken from the SQL object attribute values (to which JSON generation is applied recursively).
An asterisk wildcard (*). Example: json_object(*).

The wildcard acts as a shortcut to explicitly specifying all of the columns of a table or view, to produce the object members. The resulting JSON-object field names are the uppercase column names. You can use a wildcard with a table, a view, or a table alias, which is understood from the FROM list. The columns can be of any SQL data type.

Note the difference between this case (json_object(*)) and the case described above, where the asterisk is preceded by an explicit table or view name (or table alias), followed by a dot: json_object(t.*). In the json_object(*) case, the column names are not interpreted case-sensitively.

Another way of describing the use of asterisk wildcards with json_object is to say that it follows what is allowed for wildcards in a SQL SELECT list.

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.

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}

Note:

Because function json_object always returns JSON data, there is no need to specify FORMAT JSON for the value of input field contactInfo. But if the value of that field had been given as, for example, '{"mail":' || email ', "phone":' || phone_number || '}' then you would need to follow it with FORMAT JSON to have that string value interpreted as JSON data:

"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-4.)

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;

Related Topics

See Also:

Oracle Database SQL Language Reference for information about the select_list syntax
Oracle Database SQL Language Reference for information about SQL/JSON function json_object and the equivalent JSON constructor {…} syntax
Oracle Database SQL Language Reference for SQL identifier syntax

Parent topic: Generation of JSON Data Using SQL

23.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 values that you can use as an argument to json_array, including SQL scalar, collection instance, and user-defined object-type instance.

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 [ min_salary, max_salary ] }
  FROM jobs;

Related Topics

See Also:

Oracle Database SQL Language Reference for information about SQL/JSON function json_array and the equivalent JSON constructor […] syntax

Parent topic: Generation of JSON Data Using SQL

23.5 SQL/JSON Function JSON_OBJECTAGG

SQL/JSON function json_objectagg constructs a JSON object by aggregating information from multiple rows of a grouped SQL query as the object members.

Unlike the case for SQL/JSON function json_object, where the number of members in the resulting object directly reflects the number of arguments, for json_objectagg the size of the resulting object reflects the current queried data. It can thus vary, depending on the data that is queried.

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}

Related Topics

Overview of JSON Generation

See Also:

Oracle Database SQL Language Reference for information about SQL/JSON function json_objectagg

Parent topic: Generation of JSON Data Using SQL

23.6 SQL/JSON Function JSON_ARRAYAGG

SQL/JSON function json_arrayagg constructs a JSON array by aggregating information from multiple rows of a grouped SQL query as the array elements. The order of array elements reflects the query result order, by default, but you can use the ORDER BY clause to impose array element order.

Unlike the case for SQL/JSON function json_array, where the number of elements in the resulting array directly reflects the number of arguments, for json_arrayagg the size of the resulting array reflects the current queried data. It can thus vary, depending on the data that is queried.

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]}

Example 23-10 Generating JSON Objects with Nested Arrays Using a SQL Subquery

This example shows a SQL left outer join between two tables: countries and regions. Table countries has a foreign key, region_id, which joins with the primary key of table regions, also named region_id.

The query returns a JSON object for each row in table regions. Each of these region objects has a countries field whose value is an array of country objects — the countries in that region.

SELECT json_object(
         'region'    : region_name,
         'countries' :
         (SELECT json_arrayagg(json_object('id'   : country_id,
                                           'name' : country_name))
            FROM countries c
            WHERE c.region_id = r.region_id))
  FROM regions r;

The query results in objects such as the following:

{"region"    : "Europe",
 "countries" : [{"id"   : "BE",
                 "name" : "Belgium"},
                {"id"   : "CH",
                 "name" : "Switzerland"},
                {"id"   : "DE",
                 "name" : "Germany"},
                {"id"   : "DK",
                 "name" : "Denmark"},
                {"id"   : "FR",
                 "name" : "France"},
                {"id"   : "IT",
                 "name" : "Italy"},
                {"id"   : "NL",
                 "name" : "Netherlands"},
                {"id"   : "UK",
                 "name" : "United Kingdom"}]}

Related Topics

Overview of JSON Generation

See Also:

Oracle Database SQL Language Reference for information about SQL/JSON function json_arrayagg

Parent topic: Generation of JSON Data Using SQL

Footnote Legend

Footnote 1: The behavior of the SQL/JSON generation functions for JSON data is similar to that of the SQL/XML generation functions for XML data.