ARRAY_AGG aggregate function

The ARRAY_AGG function returns an array in which each value of the input set is assigned to an element of the array.

Syntax for ordinary array aggregation

Syntax for associative array aggregation

The schema is SYSIBM.

Ordinary array aggregation

expression

Specifies an expression that returns a value with a data type that is valid for an array element. The data type of the expression must be a data type that can be specified in a CREATE TYPE (array) statement.

Start of change expression must not contain a scalar fullselect. End of change

ORDER BY

Specifies the order of the rows from the same grouping set that are processed in the aggregation. If the ORDER BY clause is not specified, or if the ORDER BY clause cannot differentiate the order of the sort key value, the rows in the same grouping set are arbitrarily ordered.

Start of change If a SELECT clause includes multiple invocations of the ARRAY_AGG function, all invocations of ARRAY_AGG in the same SELECT clause that explicitly specify an ORDER BY clause must specify the same order, or not specify an order. End of change

sort-key-expression

Specifies a sort key value that is either a column name or an expression.

sort-key-expression must not reference a column for which a column mask is defined.

If the sort key value is a constant, the constant does not refer to the position of the output column, but is simply a constant, which implies that there is no sort key.

ASC: Processes the sort key in ascending order. This is the default option.
DESC: Processes the sort key in descending order.

Associative array aggregation

index-expression

Specifies an expression for the index of the associative array.

When the ARRAY_AGG function is invoked in a context in which there is a target user-defined array data type in the same statement, or the result of the ARRAY_AGG function 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 expression must return a value with a data type that is valid for an array element. The data type of the expression must be a data type that can be specified in a CREATE TYPE (Array) statement.

Duplicate index-expression values must not exist in the grouping set that is used to produce the associative array.

expression

The result data type of ARRAY_AGG is an array. The data type of an array element of the result array is the same as the type of expression.

Start of change The ARRAY_AGG function can be invoked only in the following contexts: End of change

The SELECT list of a SELECT INTO statement
The SELECT list of the outermost fullselect in the definition of a cursor that is not scrollable, in an SQL PL context
The SELECT list of a scalar subquery that provides a source value for a SET assignment-statement or SQL PL assignment-statement
A RETURN statement in an SQL scalar function

The following restrictions apply to ARRAY_AGG:

ARRAY_AGG cannot be used as part of an OLAP specification.
A fullselect that contains an invocation of ARRAY_AGG cannot contain an ORDER BY clause.
A fullselect that contains an invocation of ARRAY_AGG cannot contain a DISTINCT keyword in its SELECT list.
The SELECT clause or HAVING clause of the fullselect that contains an invocation of ARRAY_AGG cannot contain a subquery.
A SELECT clause that includes an invocation of the ARRAY_AGG function that returns an array of LOBs must not also include a GROUP BY clause.
A SELECT clause that includes an invocation of the ARRAY_AGG function must not also include an invocation of the LISTAGG function or the XMLAGG function.

Examples for ARRAY_AGG

Use ARRAY_AGG in an assignment statement to assign the values of the DECIMALARRAY ordinary array to the array INTARRAY.
```
SET INTARRAY = (SELECT ARRAY_AGG(VAL) FROM UNNEST(DECIMALARRAY) AS T(VAL));
```
Use ARRAY_AGG in a SELECT INTO statement to assign the values of the ESALARIES ordinary array to the array ARRAY2.
```
SELECT ARRAY_AGG(T.VAL) INTO ARRAY2 FROM UNNEST(ESALARIES) AS T(VAL);
```

Use ARRAY_AGG to aggregate a set of phone numbers into an ordinary array. The array elements are then assigned to ordinary array NUMBERS in order by priority, or in arbitrary order. Suppose that user-defined type PHONELIST and table EMPLOYEE have the following definitions:

CREATE TYPE PHONELIST AS DECIMAL(10,0) ARRAY[10];

CREATE TABLE EMPLOYEE ( ID INTEGER NOT NULL,
 PRIORITY INTEGER NOT NULL,
 PHONENUMBER DECIMAL(10,0),
 PRIMARY KEY(ID, PRIORITY)) ;

The following SQL PL procedure uses a SELECT INTO statement that returns a list of contact numbers under which an employee can be reached, ordered by priority.

CREATE PROCEDURE GETPHONENUMBERS
 (IN EMPID INTEGER,
 OUT NUMBERS PHONELIST)
 BEGIN
  SELECT ARRAY_AGG(PHONENUMBER ORDER BY PRIORITY)
   INTO NUMBERS
   FROM EMPLOYEE
  WHERE ID = EMPID;
 END

The following SQL PL procedure uses SET assignment-statement to return the list of contact numbers in an arbitrary order.

CREATE PROCEDURE GETPHONENUMBERS
 (IN EMPID INTEGER,
 OUT NUMBERS PHONELIST)
 BEGIN
  SET NUMBERS =
   (SELECT ARRAY_AGG(PHONENUMBER)
     FROM EMPLOYEE
     WHERE ID = EMPID);
 END

Use ARRAY_AGG to aggregate a set of phone numbers into an associative array. The array elements are then assigned to associative array EMPLOYEES.

Suppose that user-defined type EMPPHONES and table EMPLOYEE have the following definitions:
```
CREATE TYPE EMPPHONES AS DECIMAL(10,0) ARRAY[INTEGER];

CREATE TABLE EMPLOYEE ( ID INTEGER NOT NULL,
 PRIORITY INTEGER NOT NULL,
 PHONENUMBER DECIMAL(10,0),
 PRIMARY KEY(ID, PRIORITY)) ;
```
Create a procedure that uses a SELECT INTO statement to aggregate priority 1 phone numbers into an associative array that is indexed by IDs from the EMPLOYEE table.
```
CREATE PROCEDURE GETPHONES
(OUT EMPLOYEES EMPPHONES)
BEGIN
SELECT ARRAY_AGG(ID, PHONENUMBER)
INTO EMPLOYEES
FROM EMPLOYEE WHERE PRIORITY=1;
END
```