1. JSON Developer's Guide
  2. Generation of JSON Data
  3. 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.

  • 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.

Parent topic: Generation of JSON Data

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 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 value. You can use the optional RETURNING clause to specify a VARCHAR2 size or to specify a CLOB or BLOB return value instead. When BLOB is the return type, the character set is AL32UTF8.

Handling of Input Values For SQL/JSON Generation Functions

The generation functions take SQL values as input and render them as JSON values inside the SQL value that is returned. How the input values are rendered as JSON depends on their SQL data type. By default, a SQL NUMBER value is rendered in the output as a JSON number, a SQL VARCHAR2 value is rendered as a JSON string, and so on. For example, by default the VARCHAR2 value '{}' is rendered as the JSON string "{}".

In some cases you know or expect that an input value in fact already represents JSON data, and you want to communicate this to the generation function so that the value is kept as is. For example, if the input is '{}' then you might want it interpreted (rendered) as an empty JSON object, {}, not as a JSON string, "{}".

You can do this by adding keywords FORMAT JSON after an input expression to declare that the value that results from it is to be considered as already representing JSON data. Equivalently, you can apply SQL function treat with keywords AS JSON to a generation-function input value — the effect is the same as using FORMAT JSON.

In many cases Oracle can determine automatically that an input value is in fact JSON data, in which case the input is treated as if it were followed by an explicit FORMAT JSON declaration. This is the case, for instance, if the value expression is itself an invocation of a SQL/JSON generation function or function json_query.

If you do not specify FORMAT JSON for a given input value, 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, CLOB, or NVARCHAR value is wrapped in double quotation marks ("), and characters are escaped when necessary to conform to the JSON standard.

  • A numeric value (NUMBER, BINARY_DOUBLE, or BINARY_FLOAT) is rendered as a JSON number. (It is not quoted.)

  • A RAW or BLOB value is rendered as 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) is rendered in a supported ISO 8601 format, and the result is enclosed in double quotation marks (").

  • A BOOLEAN PL/SQL value is rendered as JSON true or false. (It is not quoted.)

  • A NULL value is rendered as JSON null, regardless of the NULL data type. (It is not quoted.)

Note:

For data types CLOB and BLOB, an empty instance is distinguished from NULL and is rendered as an empty JSON string (""). But for data types VARCHAR2, NVARCHAR2, and RAW, Oracle SQL treats an empty (zero-length) value as NULL, so do not expect such a value to be rendered as a 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 declared or automatically 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 the 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_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 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.

See Also:

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. 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 with SQL/JSON Functions

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

See Also:

Parent topic: Generation of JSON Data with SQL/JSON Functions

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

Parent topic: Generation of JSON Data with SQL/JSON Functions

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

Parent topic: Generation of JSON Data with SQL/JSON Functions

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

See Also:

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

Parent topic: Generation of JSON Data with SQL/JSON Functions


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.