Oracle JSON_ARRAY Function

The JSON_ARRAY function in Oracle SQL is a "constructor" function. Its job is the opposite of JSON_VALUE or JSON_QUERY. Instead of extracting data from JSON, JSON_ARRAY builds a new JSON array string from regular SQL values (like strings, numbers, or NULLs).

This is essential when you need to generate JSON data (for an API, a report, or to store in a CLOB column) directly from your relational data.

What is the JSON_ARRAY Function in Oracle?

The JSON_ARRAY(element1, element2, ...) function takes a list of SQL values and returns a single VARCHAR2 string containing a JSON array.

• 'Hello' (a SQL string) becomes "Hello" (a JSON string).
• 123 (a SQL number) becomes 123 (a JSON number).
• NULL (a SQL null) becomes null (a JSON null), if you use the NULL ON NULL clause.

By default, the function will omit any elements that are NULL.

JSON_ARRAY Function Syntax

The syntax for JSON_ARRAY is:

JSON_ARRAY(
    expr1 [FORMAT JSON], 
    expr2 [FORMAT JSON], ...
    [ NULL ON NULL | ABSENT ON NULL ]
    [ RETURNING data_type ]
)

Let's break that down:

• expr: The SQL value, column, or expression to include as an element in the array (e.g., 'California', 100, SYSDATE).
• FORMAT JSON (Optional): This is a critical clause. If your expr is already a JSON string (like the output of JSON_OBJECT), you must use FORMAT JSON to tell Oracle to treat it as JSON, not just a regular string.
• NULL ON NULL (Optional): If an expression is NULL, include it in the array as a JSON null value.
• ABSENT ON NULL (Optional - Default): If an expression is NULL, omit it entirely from the resulting array.

Oracle JSON_ARRAY Function Examples

Here are two practical examples of how to use JSON_ARRAY.

Example 1: Handling NULL Values with JSON_ARRAY

This example shows the difference between the default behavior (ABSENT ON NULL) and using the NULL ON NULL clause.

Query:

SELECT
  JSON_ARRAY(
    'Apple',
    'Banana',
    NULL,
    'Cherry'
    -- ABSENT ON NULL is the default, so NULL is omitted
  ) AS "Default_Behavior",

  JSON_ARRAY(
    'Apple',
    'Banana',
    NULL,
    'Cherry'
    NULL ON NULL  -- We explicitly ask to include the NULL
  ) AS "Null_On_Null_Behavior"
FROM DUAL;

Result:

Default_Behavior          Null_On_Null_Behavior
------------------------- -------------------------------
["Apple","Banana","Cherry"] ["Apple","Banana",null,"Cherry"]

Example 2: Nesting JSON Objects and Arrays using JSON_ARRAY

This is a more advanced and common example. If you want to put a JSON object (created by JSON_OBJECT) or another JSON array inside your new array, you must use the FORMAT JSON clause.

Query:

SELECT
  JSON_ARRAY (
    'California', -- A standard string
    100,          -- A standard number
    
    -- This is already JSON, so we use FORMAT JSON
    JSON_OBJECT('percentage' VALUE .50) FORMAT JSON, 
    
    -- This is also JSON, so we use FORMAT JSON
    JSON_ARRAY(1, 2, 3) FORMAT JSON
    
  ) "JSON_Array_Example"
FROM DUAL;

Result: (Notice the nested object and array are correctly embedded, not included as quoted strings.)

JSON_Array_Example
----------------------------------------------------------------------
["California",100,{"percentage":0.5},[1,2,3]]