ARRAY_AGG

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

Ordinary array aggregation

Read syntax diagramSkip visual syntax diagramARRAY_AGG( expressionORDER BY,sort-key-expressionASCDESC)
Start of change

Associative array aggregation

Read syntax diagramSkip visual syntax diagramARRAY_AGG( index-expression, expression)
End of change

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.
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 changeIf 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.
Start of change

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
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.
End of change

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 changeThe ARRAY_AGG function can be invoked only in the following contexts:End of change

  • The SELECT list of a SELECT INTO statement
  • Start of changeThe SELECT list of the outermost fullselect in the definition of a cursor that is not scrollable, in an SQL PL contextEnd of change
  • 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.
  • Start of changeA 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.End of change

Example 1: 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));

Example 2: 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);

Example 3: 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

Start of changeExample 4: Use ARRAY_AGG to aggregate a set of phone numbers into an associative array. The array elements are then assigned to associative array EMPLOYEES.End of change

Start of changeSuppose that user-defined type EMPPHONES and table EMPLOYEE have the following definitions:End of change

Start of change
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)) ;
End of change

Start of changeCreate 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.End of change

Start of change

CREATE PROCEDURE GETPHONES
(OUT EMPLOYEES EMPPHONES)
BEGIN
SELECT ARRAY_AGG(ID, PHONENUMBER)
INTO EMPLOYEES
FROM EMPLOYEE WHERE PRIORITY=1;
END
End of change