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

element-expression

Associative array aggregation

element-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

The ARRAY_AGG function can only be specified within an SQL procedure, compiled SQL function, or compound SQL (compiled) statement the following specific contexts (SQLSTATE 42887):
- The select-list of a SELECT INTO statement
- The select-list of a fullselect in the definition of a cursor that is not scrollable
- The select-list of a scalar subquery on the right side of a SET statement
ARRAY_AGG cannot be used as part of an OLAP function (SQLSTATE 42887).
The SELECT statement that uses ARRAY_AGG cannot contain an ORDER BY clause or a DISTINCT clause, and the SELECT clause or HAVING clause cannot contain a subquery or invoke an inlined SQL function that returns a subquery (SQLSTATE 42887).

Examples

Example 1: Given the following DDL:

 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))

Create a procedure that uses a SELECT INTO statement to return the prioritized list of contact numbers under which an employee can be reached.

 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

Create a procedure that uses a SET statement to return the list of contact numbers for an employee, 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

Example 2: Create a procedure that uses a SELECT INTO statement to aggregate priority 1 phone numbers into an associative array indexed by IDs from the EMPLOYEE table.

CREATE TYPE EMPPHONES AS DECIMAL(10,0) ARRAY[INTEGER]

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

Example 3: Create a procedure that uses a SELECT INTO statement to aggregate the EMPLOYEE table into an array of row variable.

CREATE TYPE EMPROW AS ROW ANCHOR ROW EMPLOYEE

CREATE TYPE EMPARRAY AS EMPROW ARRAY[]

CREATE PROCEDURE GETEMPLOYEES
   (OUT EMPLOYEES EMPARRAY)
BEGIN
  SELECT ARRAY_AGG((ID, PRIORITY, PHONENUMBER) ORDER BY ID)
  INTO EMPLOYEES
  FROM EMPLOYEE;
END