JSON_ARRAY
JSON_on_null_clause::=
JSON_returning_clause::=
Purpose
The SQL/JSON function JSON_ARRAY
takes as its input one or more SQL expressions, converts each expression to a JSON value, and returns a JSON array that contains those JSON values.
expr
For expr
, you can specify any SQL expression that evaluates to a JSON object, a JSON array, a numeric literal, a text literal, date, timestamp, or null. This function converts a numeric literal to a JSON number value, and a text literal to a JSON string value. The date and timestamp data types are printed in the generated JSON object or array as JSON Strings following the ISO 8601 date format.
FORMAT JSON
Use this optional clause to indicate that the input string is JSON, 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
- If you specify this clause, then the function returns the JSON null value. -
ABSENT
ON
NULL
- If you specify this clause, then the function omits the value from the JSON array. This is the default.
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.
Refer to JSON_OBJECT for examples.
WITH UNIQUE KEYS
Specify WITH UNIQUE KEYS
to guarantee that generated JSON objects have unique keys.
Examples
The following example constructs a JSON array from a JSON object, a JSON array, a numeric literal, a text literal, and null:
SELECT JSON_ARRAY ( JSON_OBJECT('percentage' VALUE .50), JSON_ARRAY(1,2,3), 100, 'California', null NULL ON NULL ) "JSON Array Example" FROM DUAL; JSON Array Example -------------------------------------------------------------------------------- [{"percentage":0.5},[1,2,3],100,"California",null]