Oracle JSON_ARRAY() Function

Oracle JSON_ARRAY() is a built-in function that takes each input as a JSON value and returns a JSON array containing those JSON values.

Syntax of Oracle `JSON_ARRAY()`

Here is the syntax of the Oracle JSON_ARRAY() function:

JSON_ARRAY
  ( expr [ FORMAT JSON ] [, expr [ FORMAT JSON ] ]...
   [ JSON_on_null_clause ] [ JSON_returning_clause ]
   [ STRICT ]  )

Parameters

expr

Required. You can specify any SQL expression that evaluates to a JSON object, JSON array, numeric text, text text, date, timestamp, or null. The function converts numeric text to a JSON numeric value and text text to a JSON string value. Date and timestamp data types are printed as JSON strings in ISO 8601 date format in the resulting JSON object or array.

FORMAT JSON

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

JSON_on_null_clause

Optional. You can use this clause to specify the behavior of this function when expr evaluates to null. You can use the following two clauses:

NULL ON NULL - If you specify this clause, the function returns a JSON null value.
ABSENT ON NULL - If you specify this clause, the function omits the value from the JSON array. This is the default.

JSON_returning_clause

Optional. You can use this clause to specify the type of the return value. Use one of the following values:

VARCHAR2, which specifies the size in bytes or characters. The default is bytes. If you omit this clause or specify a clause with no size value specified, JSON_ARRAY returns a character string of type VARCHAR2(4000). Note that you need to specify a size when specifying the VARCHAR2 data type in other places in SQL. However, you can omit the size in the JSON_returning_clause.
CLOB, to return a character large object that contains single-byte or multibyte characters.
BLOB, to return a binary large object with the AL32UTF8 character set.

STRICT

Optional. Specify the STRICT clause to validate whether the output of the JSON-generating function is correct JSON. If the check fails, a syntax error is raised.

Return Value

The Oracle JSON_ARRAY() function returns a JSON array containing a JSON value converted from each input parameter.

If you only provide a NULL parameter, JSON_ARRAY() returns an empty array.

Examples of Oracle `JSON_ARRAY()`

Here are several examples that demonstrate how to use the Oracle JSON_ARRAY() function.

Basic Usage

The following example constructs a JSON array from several numbers:

SELECT JSON_ARRAY(1，2，3，4)
FROM dual;

输出：

JSON_ARRAY(1，2，3，4)
______________________
[1,2,3,4]

The following example constructs a JSON array from several strings:

SELECT JSON_ARRAY('a', 'abc')
FROM dual;

输出：

JSON_ARRAY('A','ABC')
________________________
["a","abc"]

`FORMAT JSON`

The following example takes a JSON array and a JSON object as input parameters:

SELECT JSON_ARRAY('[1,2,3]', '{"x":1}')
FROM dual;

输出：

JSON_ARRAY('[1,2,3]','{"X":1}')
__________________________________
["[1,2,3]","{\"x\":1}"]

You can see that they are both quoted, which is not what you want. You can use the FORMAT JSON clause to fix this:

SELECT JSON_ARRAY('[1,2,3]' FORMAT JSON, '{"x":1}' FORMAT JSON)
FROM dual;

输出：

JSON_ARRAY('[1,2,3]'FORMATJSON,'{"X":1}'FORMATJSON)
______________________________________________________
[[1,2,3],{"x":1}]

NULL Parameters

The Oracle JSON_ARRAY() function allows you to specify how NULL parameters are handled using the ON NULL clause. The following example shows two ways to handle NULL parameters:

SELECT
    JSON_ARRAY(1，2, null, 3) "Default",
    JSON_ARRAY(1，2, null, 3 NULL ON NULL) "NULL ON NULL",
    JSON_ARRAY(1，2, null, 3 ABSENT ON NULL) "ABSENT ON NULL"
FROM dual;

输出：

Default    NULL ON NULL    ABSENT ON NULL
__________ _______________ _________________
[1,2,3]    [1,2,null,3]    [1,2,3]

Empty Array

If you provide only a NULL parameter or no parameters, JSON_ARRAY() will return an empty array.

SET NULL 'NULL';
SELECT JSON_ARRAY(NULL), JSON_ARRAY()
FROM dual;

输出：

JSON_ARRAY(NULL)    JSON_ARRAY()
___________________ _______________
[]                  []

In this example, we used the statement SET NULL 'NULL'; to display NULL values as the string 'NULL'.

Conclusion

Oracle JSON_ARRAY() is a built-in function that takes each input as a JSON value and returns a JSON array containing these values.

Syntax of Oracle JSON_ARRAY()