DB2 10.5 for Linux, UNIX, and Windows

ARRAY_AGG aggregate function

The ARRAY_AGG function aggregates a set of elements into an array.

Invocation of the ARRAY_AGG aggregate function is based on the result array type.

Ordinary array aggregation

Read syntax diagramSkip visual syntax diagram
>>-ARRAY_AGG--(--| element-expression |--+----------------------------------+--)-><
                                         |           .-,------------------. |      
                                         |           V           .-ASC--. | |      
                                         '-ORDER BY----sort-key--+------+-+-'      
                                                                 '-DESC-'          

element-expression

|--+-expression--------------------------+----------------------|
   +-row-expression----------------------+   
   |                   .-,----------.    |   
   |                   V            |    |   
   '-(--expression--,----expression-+--)-'   

Associative array aggregation

Read syntax diagramSkip visual syntax diagram
>>-ARRAY_AGG--(--index-expression--,--| element-expression |--)-><

element-expression

|--+-expression--------------------------+----------------------|
   +-row-expression----------------------+   
   |                   .-,----------.    |   
   |                   V            |    |   
   '-(--expression--,----expression-+--)-'   

The schema is SYSIBM.

Ordinary array aggregation
element-expression
Specifies the source for the elements of the array.
expression
An expression that specifies the element value for the array. The data type of the expression must be a data type that can be specified in a CREATE TYPE (array) statement (SQLSTATE 429C2).
row-expression
A row expression that specifies the value that has a row data type as the element of the array.
( expression,expression... )
A list of two or more expressions that specify the fields for a value that has a row data type as the element of the array. The data type of each expression must be a valid data type for a row field as described in CREATE TYPE (row) statement (SQLSTATE 429C5).
ORDER BY
Specifies the order of the rows from the same grouping set that are processed in the aggregation of an ordinary array. If the ORDER BY clause cannot distinguish the order of the column data, the rows in the same grouping set are arbitrarily ordered.

If ORDER BY is specified, it determines the order of the aggregated elements in the ordinary array. If ORDER BY is not specified and no other ARRAY_AGG, LISTAGG or XMLAGG is included in the same SELECT clause with ordering specified, the ordering of elements within the ordinary array is not deterministic. If ORDER BY is not specified and the same SELECT clause has multiple occurrences of ARRAY_AGG, LISTAGG, or XMLAGG that specify ordering the same ordering of elements within the ordinary array is used for each result of ARRAY_AGG.

sort-key
The sort key can be a column name or a sort-key-expression. If the sort key is a constant, it does not refer to the position of the output column (as in the ORDER BY clause of a query), but it is simply a constant that implies no sort key.
ASC
Processes the sort-key in ascending order. This is the default option.
DESC
Processes the sort-key in descending order.

The result data type is an ordinary array. If the element values are specified using a single expression or row-expression, then the data type of the array element is the same as the type of the expression or row-expression. If the element values are specified with a list of expressions, then the array element is a row type with field types that correspond to the expressions.

If a SELECT clause includes an ARRAY_AGG function, then all invocations of ARRAY_AGG, LISTAGG, XMLAGG, and XMLGROUP functions in the same SELECT clause must specify the same order or not specify an order (SQLSTATE 428GZ).

Associative array aggregation
index-expression
Specifies the index of an associative array. When used in a context where there is a target user-defined array data type in the same statement or the result of the ARRAY_AGG is explicitly cast to a user-defined array data type, the data type of index-expression must be castable to the index data type of the target associative array data type. Otherwise, the data type of the index-expression must be a data type that can be specified for the index of an associative array in a CREATE TYPE (array) statement (SQLSTATE 429C2).

There cannot be any duplicate index-expression values in the grouping set that is processed to aggregate the associative array (SQLSTATE 22545).

element-expression
Specifies the source for the elements of the array.
expression
An expression that specifies the element value for the array. The data type of the expression must be a data type that can be specified in a CREATE TYPE (array) statement (SQLSTATE 429C2).
row-expression
A row expression that specifies the value that has a row data type as the element of the array.
( expression,expression... )
A list of two or more expressions that specify the fields for a value that has a row data type as the element of the array. The data type of each expression must be a valid data type for a row field as described in CREATE TYPE (row) statement (SQLSTATE 429C5).

The result data type is an associative array. If the ARRAY_AGG is used in a context where there is a target user-defined array data type in the same statement or the result of the ARRAY_AGG is explicitly cast to a user-defined array data type, the data type of the index matches the data type of the target associative array. If the element values are specified using a single expression or row-expression, then the data type of the array element is the same as the type of the expression or row-expression. If the element values are specified with a list of expressions, then the array element is a row type with field types that correspond to the expressions.

Notes

Examples