1. SQL Language Reference
  2. Functions
  3. JSON_ARRAYAGG

JSON_ARRAYAGG

Syntax

Description of json_arrayagg.eps follows
Description of the illustration json_arrayagg.eps

(See order_by_clause::= in the documentation on SELECT for the syntax of this clause)

JSON_on_null_clause::=

Description of json_on_null_clause.eps follows
Description of the illustration json_on_null_clause.eps

JSON_agg_returning_clause::=

Description of json_agg_returning_clause.eps follows
Description of the illustration json_agg_returning_clause.eps

Purpose

The SQL/JSON function JSON_ARRAYAGG is an aggregate function. It takes as its input a column of SQL expressions, converts each expression to a JSON value, and returns a single JSON array that contains those JSON values.

expr

For expr, you can specify any SQL expression that evaluates to a JSON object, a JSON array, a numeric literal, a text literal, or null. This function converts a numeric literal to a JSON number value and a text literal to a JSON string value.

FORMAT JSON

This clause is optional and is provided for semantic clarity.

order_by_clause

This clause allows you to order the JSON values within the JSON array returned by the statement. Refer to the order_by_clause in the documentation on SELECT for the full semantics of this clause.

JSON_on_null_clause

Use this clause to specify the behavior of this function when expr evaluates to null.

  • NULL ON NULL - If you specify this clause, then the function returns the JSON null value.

  • ABSENT ON NULL - If you specify this clause, then the function omits the value from the JSON array. This is the default.

JSON_agg_returning_clause

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

  • VARCHAR2[(size [BYTE,CHAR])]

    When specifying the VARCHAR2 data type elsewhere in SQL, you are required to specify a size. However, in this clause you can omit the size.

  • CLOB

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

Refer to "Data Types" for more information on the preceding data types.

Examples

The following statements creates a table id_table, which contains ID numbers: 

CREATE TABLE id_table (id NUMBER);
INSERT INTO id_table VALUES(624);
INSERT INTO id_table VALUES(null);
INSERT INTO id_table VALUES(925);
INSERT INTO id_table VALUES(585);

The following example constructs a JSON array from the ID numbers in table id_table: 

SELECT JSON_ARRAYAGG(id ORDER BY id RETURNING VARCHAR2(100)) ID_NUMBERS
  FROM id_table;

ID_NUMBERS
-------------
[585,624,925]