JSON_ARRAYAGG() Function in Oracle

by

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