1. JSON Developer's Guide
  2. Generation of JSON Data
  3. Generation of JSON Data Using SQL

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

Note:

Besides generating JSON data from relational data explicitly, you can define a JSON-relational duality view, which automatically generates JSON documents from data in relational tables. Updating the documents supported (generated) by a duality view automatically updates the underlying relational data. Dually, updating data in the underlying tables automatically updates the documents supported by the view. See Overview of JSON-Relational Duality Views in JSON-Relational Duality Developer's Guide.

  • 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.
  • Read-Only Views Based On JSON Generation
    You can create read-only views using JSON generation functions or constructor JSON. Anyone with access can use the views as if they were read-only tables. Users of the JSON data need not know or care whether it is stored as such or generated as needed.

Parent topic: Generation of JSON Data

24.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 data that represents 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.

Note:

Oracle SQL function json_serialize consistently serializes values of Oracle JSON-language scalar types, such as float and date, using standard formats.

For example, it serializes a JSON date value using the ISO 8601 date format YYYY-MM-DD.

If you want to generate a textual JSON object or array that contains a JSON string value in some other format, then provide a string in that format as input to the generation function. (String input to json_serialize is simply output as is.)

For example, if you want json_object to produce an object with a string field that has a different ISO 8601 date format, then use SQL conversion function such as to_char to provide that string and pass it to json_object.

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. See Handling of Input Values For SQL/JSON Generation Functions.

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 inclusion in the output JSON object or array. 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.

    • EMPTY STRING ON NULL An input SQL NULL value is converted to an empty JSON string, "" for inclusion in the output JSON object or array.

  • 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, when generating textual JSON data — 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. These keywords are available only for json_object and json_objectagg.

    When generating textual JSON data, if WITH UNIQUE KEYS is absent (or if WITHOUT UNIQUE KEYS is present) then no check for unique fields is performed. In that case all fields are used, including any duplicates.

    WITH[OUT] UNIQUE KEYS has no effect when producing JSON-type data. An error is always raised when there are duplicate keys if the return type is JSON.

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 Example 24-2, Example 24-3, Example 24-4, Example 24-5, Example 24-6, Example 24-8, and Example 24-9

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 pass a SQL query expression as argument to json_array, and thus also use it as the (single) argument to JSON […]. For example, these two queries are equivalent: 

SELECT json_arrayagg(department_name)FROM departments;
SELECT json_array(SELECT department_name FROM departments) FROM DUAL;

And 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:

Parent topic: Generation of JSON Data Using SQL

24.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 the empty JSON object, {}, and not the JSON string "{}". Example 24-1 illustrates the use of FORMAT JSON to cause an input VARCHAR2 string "{\"x\":5}" to produce the JSON object {"x":5}.

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 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, Oracle nevertheless knows that the result is JSON data. In such cases using FORMAT 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 database initialization parameter 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 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 (SQL or 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.

  • A SQL VECTOR value produces a JSON-language scalar value of type vector.

Note:

For input of data types CLOB and BLOB to SQL/JSON generation functions, 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 24-1 FORMAT JSON: Declaring an Input SQL Value To Be JSON Data

In this example, PL/SQL function getX() returns a VARCHAR2 value that represents a JSON object. If FORMAT JSON were not used then the value returned by generation function json_array would be a JSON singleton array with a string element, [ "{\"x\":5} "], not a JSON object [ {"x":5} ]. 

-- PL/SQL: Return a SQL string representing a JSON object
CREATE FUNCTION getX(n NUMBER) RETURN VARCHAR2 AS
BEGIN
  RETURN '{"x":'|| n ||'}';
END;
-- SQL: Generate JSON data from SQL
SELECT json_array(getX(5) FORMAT JSON) FROM DUAL;

Using FORMAT JSON does not ensure that the input is well-formed JSON data. If you need to do that then include keyword STRICT when you use the generation function: 

SELECT json_array(getX(5) FORMAT JSON STRICT) FROM DUAL;

Related Topics

See Also:

Oracle Built-in Data Types

Parent topic: Generation of JSON Data Using SQL

24.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_mailing_address_type).

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

    You can use keywords WITH TYPENAME following the object-type instance argument. This causes the resulting object to also contain a member with field type, whose value is a string naming the user-defined type. Example 24-7 illustrates this.

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

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(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 24-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.

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(* 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 24-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'     : city,
                   'province' : state_province ABSENT ON NULL) 
  FROM hr.locations
  WHERE city LIKE 'S%';

Here is the query output:

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'     : city,
             'province' : state_province ABSENT ON NULL} 
  FROM hr.locations
  WHERE city LIKE 'S%';

Example 24-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 19-5.)

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. 

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;

This is the query output (pretty-printed here, for clarity).

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 24-7 Using WITH TYPENAME with JSON_OBJECT

This example shows the effect of using keywords WITH TYPENAME after a user-defined object argument: field type is included, with value a string naming the user-defined object type from which the JSON object was generated.

The example defines object type my_mailing_address_type, then creates a table with a column of that type and inserts a row with such an object into the table. The example assumes that the object type is created by database user (schema) user_1.

Two queries then use function json_object to generate a JSON object from the user-defined object in the table column. The second query is the same as the first, but it uses keywords WITH TYPENAME, causing the resulting object to include a member with string-valued field type. The string value is "USER_1.MY_MAILING_ADDRESS_TYPE" (showing that the type is defined and owned by schema user_1). Query output is shown here pretty-printed, for clarity. 

CREATE OR REPLACE TYPE my_mailing_address_type
  AS OBJECT(Street VARCHAR2(80),
            City   VARCHAR2(80),
            State  CHAR(2),
            Zip    VARCHAR2(10));

CREATE TABLE t1 (col1 my_mailing_address_type);

INSERT INTO t1 VALUES (my_mailing_address_type('street1', 'city1', 'CA',
    '12345'));

SELECT json_object(col1) FROM t1;
JSON_OBJECT(COL1)
-----------------
{"STREET" : "street1",
 "CITY"   : "city1",
 "STATE"  : "CA",
 "ZIP"    : "12345"}
SELECT json_object(col1 WITH TYPENAME) FROM t1;
JSON_OBJECT(COL1WITHTYPENAME)
-----------------------------
{"type"   : "USER_1.MY_MAILING_ADDRESS_TYPE",
 "STREET" : "street1",
 "CITY"   : "city1",
 "STATE"  : "CA",
 "ZIP"    : "12345"}

Related Topics

See Also:

Parent topic: Generation of JSON Data Using SQL

24.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 24-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 24-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;

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

24.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 24-10 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

See Also:

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

Parent topic: Generation of JSON Data Using SQL

24.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 24-11 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 24-12 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

See Also:

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

Parent topic: Generation of JSON Data Using SQL

24.7 Read-Only Views Based On JSON Generation

You can create read-only views using JSON generation functions or constructor JSON. Anyone with access can use the views as if they were read-only tables. Users of the JSON data need not know or care whether it is stored as such or generated as needed.

Example 24-13 illustrates this. The resulting view can be used as if it were a read-only table with columns ID (a department identification number) and DATA (JSON data for the department, including its employees). Using SQL*Plus command describe shows this: 

describe department_view;
Name   Null?      Type
---------------------------
ID     NOT NULL   NUMBER(4)
DATA              JSON

Column DATA is of JSON data type, since the special JSON constructor syntax is used (JSON {…}). The underlying stored data comes from HR sample-schema HR, tables DEPARTMENT, LOCATION, EMPLOYEES, and JOBS.

Each row of the view provides information for single department: its ID, name, address, and employees. The address of a department is a JSON object.

The data for the employees of a department is an array of employee objects, each of which has the employee's ID, full name, and job title. The name is constructed from column data that stores the first and last names separately.

Querying the view evaluates the SQL code that invokes the JSON generation functions. Example 24-14 shows a query that returns a single document, for department 90.

To improve read performance you can materialize the view.

Because JSON-generation views are read-only, you cannot update them (unless you use INSTEAD OF triggersFoot 2). If you need an updatable view that provides JSON data then you can create a JSON-relational duality view.

Example 24-13 Creating a View That Uses JSON Generation

CREATE VIEW department_view AS 
  SELECT  dep.department_id id,
          JSON {'id'                : dep.department_id,
                'departmentName'    : dep.department_name,
                'departmentAddress' : JSON {'street'  : loc.street_address,
                                            'zip'     : loc.postal_code,
                                            'city'    : loc.city,
                                            'state'   : loc.state_province,
                                            'country' : loc.country_id},
                'employees'         : [ SELECT JSON {'id'    : emp.employee_id,
                                                     'name'  : emp.first_name || ' ' || emp.last_name,
                                                     'title' : (SELECT job_title 
                                                                  FROM jobs job 
                                                                  WHERE job.job_id = emp.job_id)}
                                          FROM employees emp 
                                          WHERE emp.department_id = dep.department_id ]} data
    FROM departments dep, locations loc
    WHERE dep.location_id = loc.location_id;

Example 24-14 JSON Document Generated From DEPARTMENT_VIEW

This example pretty-prints the JSON document that is generated for department 90. Note the embedded objects (field departmentAddress and elements of array employees) that correspond to the subqueries used in the CREATE VIEW statement of Example 24-13. 

SELECT json_serialize(data pretty) FROM department_view WHERE id = 90;

{"id"                : 90,
 "departmentName"    : "Executive",
 "departmentAddress" : {"street"  : "2004 Charade Rd",
                        "zip"     : "98199",
                        "city"    : "Seattle",
                        "state"   : "Washington",
                        "country" : "US"},
 "employees"         : [ {"id"    : 100,
                          "name"  : "Steven King",
                          "title" : "President"},
                         {"id"    : 101,
                          "name"  : "Neena Kochhar",
                          "title" : "Administration Vice President"},
                         {"id"    : 102,
                          "name"  : "Lex De Haan",
                          "title" : "Administration Vice President"} ]}

See Also:

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.
Footnote 2: See INSTEAD OF DML Triggers in Oracle Database PL/SQL Language Reference for information about INSTEAD OF triggers