JSON_ARRAY() Function in Oracle

by

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, or null.
  • 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 when expr 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.