Oracle JSON_ARRAYAGG() Function

Oracle JSON_ARRAYAGG() is a built-in aggregate function that takes SQL expression columns as input, converts each expression to a JSON value, and returns a single JSON array that contains these JSON values.

Oracle JSON_ARRAYAGG() Syntax

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

JSON_ARRAYAGG
  ( expr [ FORMAT JSON ]  [ order_by_clause ]
    [ JSON_on_null_clause ] [ JSON_agg_returning_clause ]
    [ STRICT ]
  )

Parameters

expr

Required. You can specify any SQL expression that evaluates to a JSON object, JSON array, numeric literal, text literal, or null. The function converts numeric literals to JSON numeric values and text literals to JSON string values.

FORMAT JSON

Optional. Use this optional clause to indicate that the input string is JSON and is not quoted in the output.

order_by_clause

Optional. This clause allows you to sort JSON values in the JSON array returned by the statement.

JSON_on_null_clause

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

  • NULL ON NULL - If this clause is specified, the function returns a JSON null value.
  • ABSENT ON NULL - If this clause is specified, the function omits the value from the JSON array. This is the default setting.
JSON_agg_returning_clause

Optional. Use this clause to specify the data type of the string returned by the function. You can specify the following data types:

  • VARCHAR2[(size [BYTE,CHAR])]: When specifying the VARCHAR2 data type elsewhere in SQL, you must specify the size. However, in this clause, you can omit the size.
  • CLOB

If you omit this clause or specify VARCHAR2 but omit the size value, JSON_ARRAYAGG returns a string of type VARCHAR2(4000).

STRICT

Optional. Specify the STRICT clause to validate that the output of the JSON generation function is valid JSON. If the check fails, a syntax error is raised.

Return Value

The Oracle JSON_ARRAYAGG() function returns a JSON array that includes all the computed results based on the given expression.

Oracle JSON_ARRAYAGG() Examples

This example simulates a data table using the following statement:

SELECT 'Tim' name, 'Football' hobby FROM DUAL
UNION ALL
SELECT 'Tim', 'Basketball' FROM DUAL
UNION ALL
SELECT 'Lucy', 'Piano' FROM DUAL
UNION ALL
SELECT 'Lucy', 'Violin' FROM DUAL;

输出：

NAME    HOBBY
_______ _____________
Tim     Football
Tim     Basketball
Lucy    Piano
Lucy    Violin

If you need to get all the hobby information from the above dataset, use the following statement:

SELECT JSON_ARRAYAGG(hobby)
FROM (
    SELECT 'Tim' name, 'Football' hobby FROM DUAL
    UNION ALL
    SELECT 'Tim', 'Basketball' FROM DUAL
    UNION ALL
    SELECT 'Lucy', 'Piano' FROM DUAL
    UNION ALL
    SELECT 'Lucy', 'Violin' FROM DUAL
);

输出：

JSON_ARRAYAGG(HOBBY)
_____________________________________________
["Football","Basketball","Piano","Violin"]

If you need to retrieve hobby information for each person from the dataset above, you can use the following statement with a GROUP BY clause:

SELECT name, JSON_ARRAYAGG(hobby) hobbies
FROM (
    SELECT 'Tim' name, 'Football' hobby FROM DUAL
    UNION ALL
    SELECT 'Tim', 'Basketball' FROM DUAL
    UNION ALL
    SELECT 'Lucy', 'Piano' FROM DUAL
    UNION ALL
    SELECT 'Lucy', 'Violin' FROM DUAL
)
GROUP BY name;

输出：

NAME    HOBBIES
_______ ____________________________
Lucy    ["Piano","Violin"]
Tim     ["Football","Basketball"]

Alternatively, you can also sort all the hobbies in ascending order:

SELECT name, JSON_ARRAYAGG(hobby ORDER BY hobby) hobbies
FROM (
    SELECT 'Tim' name, 'Football' hobby FROM DUAL
    UNION ALL
    SELECT 'Tim', 'Basketball' FROM DUAL
    UNION ALL
    SELECT 'Lucy', 'Piano' FROM DUAL
    UNION ALL
    SELECT 'Lucy', 'Violin' FROM DUAL
)
GROUP BY name;

输出：

NAME    HOBBIES
_______ ____________________________
Lucy    ["Piano","Violin"]
Tim     ["Basketball","Football"]

Conclusion

Oracle’s JSON_ARRAYAGG() is a built-in aggregate function that takes SQL expression columns as input, converts each expression into a JSON value, and returns a single JSON array containing these JSON values.