collection-derived-table
A collection-derived table is used to convert the elements of one or more arrays into column values in separate rows of an intermediate result table.
collection-derived-table:
- WITH ORDINALITY
- Specifies that an extra column of data type INTEGER is returned as the last column in the result table. This column contains the position of the element in the array.
- correlation-clause
- Specifies the correlation name that is to be used as a table designator
for the result table of the collection derived table, and a list of
column names for the result table. The correlation name can be used
to qualify references to the columns of the result table.
The result columns can be referenced in the SELECT list, and in the rest of the subselect by using the names that are specified for the columns in the correlation clause.
A collection-derived table can be specified as a table reference in a FROM clause, in a context where arrays are supported.
The intermediate result table is derived as follows:
- If a single expression that returns an ordinary array is specified, the intermediate result table is a single-column table with a column data type that matches the array element data type.
- If multiple expressions that return an ordinary array are specified:
- 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, UNNEST performs an outer join among the arrays, using equality on the array indexes as a join predicate.
- If a single associative-array-expression or
an array-function-invocation that returns an associative
array is specified:
- The intermediate result table is a table with two 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.
- The first column contains the indexes of the elements in the array.
- The second column contains the elements in the array.
- The columns can be referenced in the SELECT list and the in rest of the subselect by using the names that are specified for the columns in the correlation-clause.
- If all arguments are null arrays, the result is an empty table.
The intermediate result table that is produced by an invocation of UNNEST must not result in more than 750 columns.
An array-function-invocation is a function invocation that resolves to a function that returns an ordinary or an associative array type. An array-function-invocation must not include a reference to a column of a common table expression.
- ordinary-array-expression
- Specifies one of the following items:
- An SQL variable
- An SQL parameter
A global variable of an
ordinary array type
- An array-function-invocation
- A CAST specification of a parameter marker to an ordinary array type
- associative-array-expression
- Specifies one of the following items:
- An SQL variable
- An SQL parameter
- A global variable of an
associative array type
- An array-function-invocation
- A CAST specification of a parameter marker to an associative array type
Names for the result columns that are produced by an UNNEST specification can be provided as part of the correlation-clause of the collection-derived-table clause.
Example 1: Suppose that PHONENUMBERS is a user-defined array type that is defined as an ordinary array. RECENT_CALLS is an array variable of the PHONENUMBERS type. RECENT_CALLS contains the following phone numbers:
- 9055553907
- 4165554213
- 4085553678
The following SELECT statement uses UNNEST to retrieve the list of phone numbers from the array:
SELECT T.ID, T.NUM
FROM UNNEST(RECENT_CALLS) WITH ORDINALITY AS T(NUM, ID);The WITH ORDINALITY clause indicates that the result table is to include an additional column that reflects the ordinal position of each array element in the array. The additional column is the last column of the result table from the UNNEST operation. The correlation clause that follows the WITH ORDINALITY clause specifies that the additional column is named ID, and the array element column is named NUM. These column names can be explicitly referenced in the SELECT list of the query. The SELECT list in this example reorders the columns from the result of UNNEST. The result table looks like this:
| ID | NUM |
|---|---|
| 1 | 9055553907 |
| 2 | 4165554213 |
| 3 | 4085553678 |
In the SELECT statement, the columns that result from the UNNEST operation have been reordered in the SELECT list, so that the column that reflects the position of each array element is the first column of the final result table.
SET PHONELIST['Home'] = '4443051234';
SET PHONELIST['Work'] = '4443052345';
SET PHONELIST['Cell'] = '4447893456';The following SELECT statement is executed:
SELECT T.ID, T.PHONE
FROM UNNEST(PHONELIST) AS T(ID, PHONE);The result table looks like this, although the order of rows might differ:
| ID | PHONE |
|---|---|
| Cell | 4447893456 |
| Home | 4443051234 |
| Work | 4443052345 |
Example 3: Suppose that PHONES and IDS are two SQL variables with array values of the same cardinality. The following SQL statement converts the array contents into a table with three columns (one for each array and one for the position), and one row for each array element.
The following SELECT statement is executed:
SELECT T.PHONE, T.ID, T.INDEX FROM UNNEST(PHONES, IDS)
WITH ORDINALITY AS T(PHONE, ID, INDEX)
ORDER BY T.INDEX;