In Oracle Database, the JSON_ARRAY()
function creates a JSON array from a sequence of SQL scalar expressions or one collection type instance, VARRAY
or NESTED TABLE
.
Syntax
The syntax goes like this:
JSON_ARRAY
( expr [ FORMAT JSON ] [, expr [ FORMAT JSON ] ]...
[ JSON_on_null_clause ] [ JSON_returning_clause ]
[ STRICT ] )
Where:
expr
is any SQL expression that evaluates to a JSON object, a JSON array, a numeric literal, a text literal, date, timestamp, ornull
.FORMAT JSON
indicates that the input string is JSON, and will therefore not be quoted in the output.JSON_on_null_clause
specifies the behaviour to use whenexpr
contains a null value (i.e. either include null values in the output or don’t).JSON_returning_clause
specifies the type of return value.STRICT
checks whether or not the output of the JSON generation function is correct JSON. If the check fails, a syntax error is raised.
Example
Here’s an example to demonstrate how it works:
SELECT JSON_ARRAY(1, 2, 3) FROM DUAL;
Result:
[1,2,3]
Nested
You can also nest JSON_ARRAY()
functions within another JSON_ARRAY()
function:
SELECT JSON_ARRAY(
JSON_ARRAY(1, 2, 3),
JSON_ARRAY(4, 5, 6)
)
FROM DUAL;
Result:
[[1,2,3],[4,5,6]]
The FORMAT JSON
Clause
You can use the FORMAT JSON
clause to indicate that the input string is JSON, and should therefore not be quoted in the output.
Here’s an example to demonstrate:
SELECT
JSON_ARRAY('{"a":1}') AS r1,
JSON_ARRAY('{"a":1}' FORMAT JSON) AS r2
FROM DUAL;
Result:
R1 R2 ________________ ____________ ["{\"a\":1}"] [{"a":1}]
Null Values
You can specify whether or not null values are included in the resulting array.
To include them, use NULL ON NULL
:
SELECT
JSON_ARRAY(1, null, 3 NULL ON NULL)
FROM DUAL;
Result:
[1,null,3]
To exclude them, use ABSENT ON NULL
:
SELECT
JSON_ARRAY(1, null, 3 ABSENT ON NULL)
FROM DUAL;
Result:
[1,3]
ABSENT ON NULL
is the default.
See Oracle’s documentation for a more detailed explanation of this function.