JSON_OBJECT
JSON_on_null_clause::=
JSON_returning_clause::=
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. Forexpr
, 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. Ifexpr
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.
FORMAT JSON
Specify FORMAT JSON
after an input expression to declare that the value that results from it represents JSON data, and will therefore not be quoted in the output.
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
Use this clause to specify the type of return value. One of :
-
VARCHAR2
specifying the size as a number of bytes or characters. The default is bytes. If you omit this clause, or specify the clause without specifying thesize
value, thenJSON_ARRAY
returns a character string of typeVARCHAR2(4000)
. Refer to VARCHAR2 Data Type for more information. Note that when specifying theVARCHAR2
data type elsewhere in SQL, you are required to specify a size. However, in theJSON_returning_clause
you can omit the size. -
CLOB
to return a character large object containing single-byte or multi-byte characters. -
BLOB
to return a binary large object of theAL32UTF8
character set.
STRICT
Specify the STRICT
clause to verify that the output of the JSON generation function is correct JSON. If the check fails, a syntax error is raised.
Example 1: Output string appears within quotes, because FORMAT JSON
is not used
SELECT JSON_OBJECT ('name' value 'Foo') FROM DUAL Output: JSON_OBJECT('NAME'VALUE'FOO'FORMATJSON) ------------------------------------------------- {"name":"Foo"}
Example 2: No quotes around output string when FORMAT JSON
is used.
SELECT JSON_OBJECT ('name' value 'Foo' FORMAT JSON ) FROM DUAL Output: JSON_OBJECT('NAME'VALUE'FOO'FORMATJSON) ------------------------------------------------- {"name":Foo}
Example 3:JSON Syntax error when FORMAT JSON STRICT
is used.
SELECT JSON_OBJECT ('name' value 'Foo' FORMAT JSON STRICT ) FROM DUAL Output: ORA-40441: JSON syntax error
WITH UNIQUE KEYS
Specify WITH UNIQUE KEYS
to guarantee that generated JSON objects have unique keys.
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"}
. . .