7.23.15 ARRAY_AGG

(1) Specification format

ARRAY_AGG-set-function::= ARRAY_AGG(target-data [ORDER BY sort-specification-list])
 
  target-data::= value-expression

To Page Top

(2) Explanation of specification format

target-data:

Specifies the value expression to be aggregated for the target data.

The following specification rules apply:

• You cannot specify array data and structure data for the value expression.

• You cannot specify a dynamic parameter by itself for the value expression.

ORDER BY sort-specification-list:

Specified the sort order of the values to be aggregated in the sort specification list. This specification allows the values of array elements to be ordered in ascending or descending order.

If this specification is omitted, the order of values in the execution result of ARRAY_AGG may change (the order of values of array elements may change) even if the same SQL statement is executed.

For details about the sort specification list, see 7.25 Sort specification list.

The following specification rules apply:

• The null-value sort order specification cannot be specified in the sort specification list of ARRAY_AGG.

• No more than one sort specification is permitted in the sort specification list in ARRAY_AGG.

To Page Top

(3) Rules

1. The data type of the ARRAY_AGG execution result is an array type. The element data type will be the data type of the value expression specified in the target data. The maximum number of elements is 30,000.

2. Up to 256 ARRAY_AGG can be specified during a query specification.

3. If the sort specification list is omitted, the aggregated string derived from the value expression specified for the target data will be the input line for ARRAY_AGG. The order of the values that will be in the input line is not guaranteed.

4. When a sort specification list is specified, the result of sort processing according to the sort specification on the target values derived from the value expressions specified for the target data will be the input line of ARRAY_AGG.

   If the aggregated string has a null value, the treatment of the null value differs depending on the order specification of the sort specification list as follows.

   • If you specify ASC for order specification or if you do not specify order specification

     The null value comes last.

   • If you specify DESC for order specification

     The null value comes first.

5. When determining the input rows for ARRAY_AGG, null values included in the aggregate are not eliminated.

6. If the number of ARRAY_AGG input rows is 0, the execution result of ARRAY_AGG will be a null value.

7. The execution result of ARRAY_AGG is array data with the values contained in the input rows as array elements in order from the beginning.

8. If the number of input rows for ARRAY_AGG exceeds 30,000, the SQL statement will become an error.

To Page Top

(4) Examples

The following are examples of executing a SQL statement to search the sales table (SALES) shown below.

Examples

Retrieve the sales table (SALES) and determine the array data with product ID (PID) as array element. The conditions for the array data to be determined are as follows.

• Order array element values in ascending order of unit price (PRICE).

• Determine array data for each customer ID (CID).

SELECT "CID",ARRAY_AGG("PID" ORDER BY "PRICE") AS "PID_LIST"
  FROM "SALES"
    GROUP BY "CID"

Execution result example

To Page Top