In Oracle Database, the JSON_ARRAYAGG()
function creates a JSON array from a column of SQL expressions.
Syntax
The syntax goes like this:
JSON_ARRAYAGG
( expr [ FORMAT JSON ] [ order_by_clause ]
[ JSON_on_null_clause ] [ JSON_agg_returning_clause ]
[ STRICT ]
)
Where:
expr
is any SQL expression that evaluates to a JSON object, a JSON array, a numeric literal, a text literal, ornull
.FORMAT JSON
indicates that the input string is JSON, and will therefore not be quoted in the output.order_by_clause
allows you to order the JSON values within the JSON array returned by the statement.JSON_on_null_clause
specifies the behaviour to use whenexpr
evaluates to null (i.e. either include null values in the output or don’t).JSON_agg_returning_clause
specifies the data type of the character string returned by this function.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
Suppose we run the following SQL query:
SELECT region_name
FROM regions;
And it returns the following output:
REGION_NAME _________________________ Europe Americas Asia Middle East and Africa
If we wanted all those values to be elements in a JSON array, we could pass the column name to the JSON_ARRAYAGG()
function.
Like this:
SELECT JSON_ARRAYAGG(region_name)
FROM regions;
Result:
["Europe","Americas","Asia","Middle East and Africa"]
Order By Clause
Here’s an example of using the ORDER BY
clause within the function:
SELECT JSON_ARRAYAGG(
region_name ORDER BY region_name DESC
)
FROM regions;
Result:
["Middle East and Africa","Europe","Asia","Americas"]
That example sorted the array elements in descending order.
Here it is in ascending order:
SELECT JSON_ARRAYAGG(
region_name ORDER BY region_name ASC
)
FROM regions;
Result:
["Americas","Asia","Europe","Middle East and Africa"]
See the Oracle documentation for more information about this function.