The UNNEST function returns a result table that includes a row for each element of the specified array.
If there are multiple ordinary array arguments specified, the number of rows will match the array with the largest cardinality.

The schema is SYSIBM.

array-expression

An expression that returns an array data type.
The expression must be one of the following expressions:

• An SQL variable
• An SQL parameter
• A global variable
• A function invocation
• A CAST specification of a parameter marker
• A CASE expression

Names for the result columns produced by the UNNEST function can be provided as part of the correlation-clause of the collection-derived-table clause.

The UNNEST function can only be used in a collection-derived-table clause in a context where arrays are supported (SQLSTATE 42887).

The result table depends on the input arguments.

• If a single array-expression that returns an ordinary array is specified:
  • If the array element is not a row data type, the result is a single column table with a column data type that matches the array element data type.
  • If the array element is a row data type, the result is a table with one column for each row field in the element data type.
    The result table column data types match the corresponding array element row field data types.
• If more than one ordinary array argument is specified and none of the array elements have a row data type, the first array provides the first column in the result table, the second array provides the second column, and so on.
  The data type of each column matches the data type of the array elements of the corresponding array argument.
  If the cardinalities of the arrays are not identical, the cardinality of the resulting table is the same as the array with the largest cardinality.
  The column values in the table are set to the null value for all rows whose array index value is greater than the cardinality of the corresponding array.
  In other words, if each array is viewed as a table with two columns, one for the array indexes and one for the data, then UNNEST performs an OUTER JOIN among the arrays, using equality on the array indexes as a join predicate.
• If a single array-expression that returns an associative array is specified:
  • If the array element is not a row data type, the result is a table with 2 columns where the first column data type matches the array index data type and the second column data type matches the array element data type.
  • If the array element is a row data type, the result is a table with one more column than the number of fields in the row data type, where the first column data type matches the array index data type and the remaining column data types match the array element row field data types.
• An error is returned (SQLSTATE 42884):
  • If more than one associative array argument is specified.
  • If more than one array argument is specified and at least one of the arrays has a element data type that is a row type.
  • If both ordinary array arguments and associative array arguments are specified.

This special table function is only used in collection-derived-table of table-reference in a FROM clause.

If more than one array is provided and at least one of the arguments is an associative array, an error is returned (SQLSTATE 42884).

If the WITH ORDINALITY clause is used when unnesting an associative array, an error is returned (SQLSTATE 428HT).

Examples

1. Assume the ordinary array variable RECENT_CALLS of array type PHONENUMBERS contains only the three element values 9055553907, 4165554213, and 4085553678.
   The following query:

      SELECT T.ID
      ,      T.NUM
      FROM   UNNEST(RECENT_CALLS) WITH ORDINALITY AS T(NUM, ID)

   returns a table formatted as follows:

      ID    NUM
      ----  ----
      1     9055553907
      2     4165554213
      3     4085553678

2. Return the list of personal phone numbers from the array variable PHONELIST of array type PERSONAL_PHONENUMBERS along with the index string values.
   The following query:

      SELECT T.ID
      ,      T.PHONE
      FROM   UNNEST(PHONELIST) AS T(ID, PHONE)

   returns a table formatted as follows:

      ID     PHONE
      -----  ----------
      Home   4163053745
      Work   4163053746
      Mom    4164789683 

Related reference

• CARDINALITY scalar function