Generation of JSON Data With SQL/JSON Functions

19 Generation of JSON Data With SQL/JSON Functions

SQL/JSON functions json_object, json_array, json_objectagg, and json_arrayagg are presented.

Topics:

Overview of SQL/JSON Generation Functions
You can use SQL/JSON functions json_object, json_array, json_objectagg, and json_arrayagg to construct JSON data from non-JSON data in the database. The JSON data is returned as a SQL value.
JSON_OBJECT SQL/JSON Function
SQL/JSON function json_object constructs JSON objects from name–value pairs. Each pair is provided as an explicit argument. Each name of a pair must evaluate to a SQL identifier. Each value of a pair can be any SQL expression. The name and value are separated by keyword VALUE.
JSON_ARRAY SQL/JSON Function
SQL/JSON function json_array constructs a JSON array from the results of evaluating its argument SQL expressions. Each argument can be any SQL expression. Array element order is the same as the argument order.
JSON_OBJECTAGG SQL/JSON Function
SQL/JSON function json_objectagg constructs a JSON object by aggregating information from multiple rows of a grouped SQL query as the object members.
JSON_ARRAYAGG SQL/JSON Function
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.

19.1 Overview of SQL/JSON Generation Functions

You can use SQL/JSON functions json_object, json_array, json_objectagg, and json_arrayagg to construct JSON data from non-JSON data in the database. The JSON data is returned as a SQL value.

These generation functions 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 these functions. 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 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 these 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, given as arguments SQL name–value pairs and values, respectively. The number of arguments corresponds to the number of object members and array elements, respectively (except when an argument expression evaluates to SQL NULL and the ABSENT ON NULL clause applies).

Each name must have the syntax of a SQL identifier. Each value can be any SQL value, including a value computed using a scalar SQL (sub)query that returns at most one item (a single row with a single column — an error is raised if such a query argument returns more than one row.)
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, the order of object members is unspecified. For json_arrayagg, the order of array elements reflects the query result order. You can use SQL ORDER BY in the query to control the array element order.

Formats of Input Values for JSON_OBJECT and JSON_ARRAY

For function json_array you can use any SQL value of the supported data types as arguments. Similarly for the value arguments of name–value pairs that you pass to function json_object. In some cases you know or expect that such a value is in fact JSON data (represented as a SQL string or number). You can add keywords FORMAT JSON after any input value expression to declare this expectation for the value that results from that expression.

If Oracle can determine that the value is in fact JSON data then it is treated as if it were followed by an explicit FORMAT JSON declaration. This is the case, for instance, if the value expression is an invocation of a SQL/JSON generation function.

If you do not specify FORMAT JSON, and if Oracle cannot determine that the value is JSON data, then it is assumed to be ordinary (non-JSON) SQL data. In that case it is serialized as follows (any other SQL value raises an error):

A VARCHAR2 or CLOB value is wrapped in double quotation marks (").
A numeric value is converted to a JSON number. (It is not quoted.)
A DATE or TIMESTAMP value is converted to ISO 8601 format, and the result is enclosed in double quotation marks (").
A BOOLEAN PL/SQL value is converted to JSON true or false. (It is not quoted.)
A NULL value is converted to JSON null, regardless of the NULL data type.

Note:

Because Oracle SQL treats an empty string as NULL there is no way to construct an empty JSON string ("").

The format of an input argument can affect the format of the data that is returned by the function. In particular, if an input is determined to be of format JSON then it is treated as JSON data when computing the return value. Example 19-1 illustrates this — it explicitly uses FORMAT JSON to interpret the SQL string "true" as JSON Boolean value true.

Optional Behavior For SQL/JSON Generation Functions

You can optionally specify a SQL NULL-handling clause, a RETURNING clause, and keyword STRICT.

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_array and json_arrayagg.
- ABSENT ON NULL — An input SQL NULL value results in no corresponding output. This is the default behavior for json_object and json_objectagg.
RETURNING clause — The SQL data type used for the function return value. The default 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.

Result Returned by SQL/JSON Generation Functions

The generated JSON data is returned from the function as a SQL VARCHAR2 value, whose size can be controlled by the optional RETURNING clause. For the aggregate SQL functions (json_objectagg and json_arrayagg), you can also specify CLOB as the SQL data type in the RETURNING clause.

JSON values within the returned data are derived from SQL values in the input as follows:

A SQL number is converted to a JSON number.
A non-NULL and non-number SQL value is converted to a JSON string.
A SQL NULL value is handled by the optional NULL-handling clause.

See Also:

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

Example 19-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.

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}

19.2 JSON_OBJECT SQL/JSON Function

SQL/JSON function json_object constructs JSON objects from name–value pairs. Each pair is provided as an explicit argument. Each name of a pair must evaluate to a SQL identifier. Each value of a pair can be any SQL expression. The name and value are separated by keyword VALUE.

The evaluated arguments you provide to json_object are explicit object field names and field values. The resulting object has an member for each pair of name–value arguments you provide (except when an value expression evaluates to SQL NULL and the ABSENT ON NULL clause applies).

Example 19-2 Using JSON_OBJECT to Construct JSON Objects

This example constructs a JSON object for each employee of table hr.employees (from standard database schema HR) whose salary is less than 15000. The object includes, as the value of its field contactInfo, an object with fields mail and phone.

Because the return value of json_object is JSON data, FORMAT JSON is deduced for the input format of field contactInfo — the explicit FORMAT JSON here is not needed.

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

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

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

Example 19-3 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, the 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' VALUE state_province ABSENT ON NULL) 
  FROM 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"}

Related Topics

Overview of SQL/JSON Generation Functions

See Also:

Oracle Database SQL Language Reference for information about SQL/JSON function json_object
Oracle Database SQL Language Reference for SQL identifier syntax

19.3 JSON_ARRAY SQL/JSON Function

SQL/JSON function json_array constructs a JSON array from the results of evaluating its argument SQL expressions. Each argument can be any SQL expression. Array element order is the same as the argument order.

The evaluated arguments you provide to json_array are explicit array element values. 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).

An argument expression that evaluates to a SQL number is converted to a JSON number. A non-NULL and non-number argument value is converted to a JSON string.

Example 19-4 Using JSON_ARRAY to Construct a JSON Array

This example constructs a JSON object for each 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.

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

JSON_OBJECT('TITLE'ISJOB_TITLE,'SALARYRANGE'ISJSON_ARRAY(MIN_SALARY,MAX_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]}

Related Topics

See Also:

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

19.4 JSON_OBJECTAGG SQL/JSON Function

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 19-5 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.

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}

See Also:

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

19.5 JSON_ARRAYAGG SQL/JSON Function

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

Related Topics

Overview of SQL/JSON Generation Functions

See Also:

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

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.