Syntax

Description of the illustration json_object.eps

JSON_on_null_clause::=

Description of the illustration json_on_null_clause.eps

JSON_returning_clause::=

Description of the illustration json_returning_clause.eps

Purpose

The SQL/JSON function JSON_OBJECT takes as its input one or more property key-value pairs. It returns a JSON object that contains an object member for each of those key-value pairs.

[KEY] string VALUE expr

Use this clause to specify a property key-value pair.

• KEY is optional and is provided for semantic clarity.

• Use string to specify the property key name as a case-sensitive text literal.

• Use expr to specify the property value. For expr, you can specify any expression that evaluates to a SQL numeric literal, text literal, date, or timestamp. The date and timestamp data types are printed in the generated JSON object or array as JSON strings following the ISO date format. If expr evaluates to a numeric literal, then the resulting property value is a JSON number value; otherwise, the resulting property value is a case-sensitive JSON string value enclosed in double quotation marks.

JSON_on_null_clause

Use this clause to specify the behavior of this function when expr evaluates to null.

• NULL ON NULL - When NULL ON NULL is specified, then a JSON NULL value is used as a value for the given key.

  SELECT JSON_OBJECT('key1' VALUE NULL)  evaluates to  {"key1" :  null}

• ABSENT ON NULL - If you specify this clause, then the function omits the property key-value pair from the JSON object.

JSON_returning_clause

The character string returned by this function is of data type VARCHAR2. This clause allows you to specify the size of the VARCHAR2 data type. Use BYTE to specify the size as a number of bytes or CHAR to specify the size as a number of characters. The default is BYTE. If you omit this clause, or if you specify this clause but omit the size value, then JSON_OBJECT returns a character string of type VARCHAR2(4000).

Refer to VARCHAR2 Data Type for more information. Note that when specifying the VARCHAR2 data type elsewhere in SQL, you are required to specify a size. However, in the JSON_returning_clause you can omit the size.

WITH UNIQUE KEYS

Use this option to discover duplicate key names in a generated JSON object. If duplicate key names are found, an error message is raised. This check has performance implications and is limited to 8192 keys per object level.

Examples

The following example returns JSON objects that each contain two property key-value pairs:

SELECT JSON_OBJECT (
    KEY 'deptno' VALUE d.department_id,
    KEY 'deptname' VALUE d.department_name 
    ) "Department Objects"
  FROM departments d
  ORDER BY d.department_id;

Department Objects
----------------------------------------
{"deptno":10,"deptname":"Administration"}
{"deptno":20,"deptname":"Marketing"}
{"deptno":30,"deptname":"Purchasing"}
{"deptno":40,"deptname":"Human Resources"}
{"deptno":50,"deptname":"Shipping"}
. . .