The SQL/JSON function JSON_ARRAY takes as its input a sequence of SQL scalar expressions or one collection type instance, VARRAY or NESTED TABLE.

It converts each expression to a JSON value, and returns a JSON array that contains those JSON values.

If an ADT has a member which is a collection than the type mapping creates a JSON object for the ADT with a nested JSON array for the collection member.

If a collection contains ADT instances then the type mapping will create a JSON array of JSON objects.


Use this clause to define the input to the JSON_ARRAY function.


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

    You can specify FORMAT JSON to indicate that the input string is JSON, and will therefore not be quoted in the output.


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.


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 the size value, then JSON_ARRAY 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.

  • CLOB to return a character large object containing single-byte or multi-byte characters.

  • BLOB to return a binary large object of the AL32UTF8 character set.


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.


The following example constructs a JSON array from a JSON object, a JSON array, a numeric literal, a text literal, and null:

    JSON_OBJECT('percentage' VALUE .50),
    ) "JSON Array Example"
JSON Array Example