Movatterモバイル変換

Syntax

Description of the illustration json_arrayagg.eps

(Seeorder_by_clause::= in the documentation onSELECT for the syntax of this clause)

JSON_on_null_clause::=

Description of the illustration json_on_null_clause.eps

JSON_agg_returning_clause::=

Description of the illustration json_agg_returning_clause.eps

Purpose

The SQL/JSON functionJSON_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

Forexpr, 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

JSON_on_null_clause

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

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

• ABSENTONNULL - 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 theVARCHAR2 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 specifyVARCHAR2 but omit thesize value, thenJSON_ARRAYAGG returns a character string of typeVARCHAR2(4000).

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

Examples

The following statements creates a tableid_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 tableid_table:

SELECT JSON_ARRAYAGG(id ORDER BY id RETURNING VARCHAR2(100)) ID_NUMBERS  FROM id_table;ID_NUMBERS-------------[585,624,925]