Oracle JSON_ARRAYAGG Function

The JSON_ARRAYAGG function in Oracle SQL is an aggregate function. Its job is to take all the values from a single column across multiple rows and combine (or "aggregate") them into a single JSON array.

This is the "JSON version" of the LISTAGG function and is the partner to JSON_OBJECTAGG (which creates an object). It's the opposite of JSON_TABLE, which shreds an array into rows.

What is the JSON_ARRAYAGG Function in Oracle?

The JSON_ARRAYAGG(expr) function takes a column as input and returns a single VARCHAR2, CLOB, or JSON value that contains a JSON array of all the values from that column.

This is essential for:

• "Rolling up" child records into an array for a parent record.
• Creating a single JSON document from a GROUP BY query.
• Combining many rows of data into a single string.

JSON_ARRAYAGG Function Syntax

The syntax for JSON_ARRAYAGG is:

JSON_ARRAYAGG(
    expr [FORMAT JSON]
    [ORDER BY ...]
    [ NULL ON NULL | ABSENT ON NULL ]
    [ RETURNING data_type ]
)

Let's break that down:

• expr: The column or value you want to aggregate into the array (e.g., employee_id).
• [FORMAT JSON] (Optional): Use this if the expr is already JSON (like the output of JSON_OBJECT). This prevents Oracle from double-quoting it as a string.
• [ORDER BY ...] (Optional, but Highly Recommended): This is critical. It controls the order of the elements inside the final array.
• [NULL ON NULL | ABSENT ON NULL]:
  • ABSENT ON NULL (Default): If a row's value is NULL, it is simply omitted from the array.
  • NULL ON NULL: If a row's value is NULL, it is included as a JSON null in the array.
• [RETURNING data_type] (Optional): Specifies the return type, such as VARCHAR2(4000) (default), CLOB, or JSON.

Oracle JSON_ARRAYAGG Function Examples

Because JSON_ARRAYAGG is an aggregate function that works on multiple rows, we must first simulate a multi-row table. We can do this using the DUAL table with UNION ALL.

Example 1: Aggregating a Column with JSON_ARRAYAGG

This is the most common use case. Let's take a list of ID numbers (from 3 different "rows") and roll them up into a single JSON array, ordered from lowest to highest.

Query:

-- First, we simulate a table 'id_table' with 3 rows using DUAL
WITH id_table AS (
  SELECT 624 AS id FROM DUAL UNION ALL
  SELECT 925 AS id FROM DUAL UNION ALL
  SELECT 585 AS id FROM DUAL
)
-- Now, we aggregate the 'id' column from our simulated table
SELECT
  JSON_ARRAYAGG(id ORDER BY id ASC) AS "ID_Numbers_Array"
FROM id_table;

Result: (The 3 rows are combined into one array, ordered as requested)

ID_Numbers_Array
-----------------
[585,624,925]

Example 2: Using FORMAT JSON and NULL ON NULL with JSON_ARRAYAGG

This example shows how to aggregate values that are already JSON objects, and how to include NULL values in the final array.

Query:

WITH item_table AS (
  SELECT JSON_OBJECT('id' VALUE 624) AS item FROM dual UNION ALL
  SELECT NULL AS item FROM dual UNION ALL
  SELECT JSON_OBJECT('id' VALUE 585) AS item FROM dual
)
SELECT JSON_ARRAYAGG(item FORMAT JSON NULL ON NULL) AS "Item_Array"
FROM (
  SELECT item
  FROM item_table
  ORDER BY item
);

Result: (The NULL is included, and the objects are not double-quoted)

Item_Array
--------------------------------------
[{"id":585},{"id":624},null]