UNNEST array operator

UNNEST ( array-expression [, ...] ) 
[ WITH ORDINALITY ]

array-expression An array to derive a table column from.
WITH ORDINALITY The WITH ORDINALITY clause permits the application to recall the original array element from which each value was obtained. Valid UNNEST derived tables must have names specified (by using the AS clause) for each of the resulting expressions. The order of the resulting rows from unnest is not guaranteed. Users can achieve a desired ordering with an ORDER BY clause.

If the array expressions have different cardinalities, the missing output expressions from the shorter array(s) are set to NULL. If the WITH ORDINALITY clause is specified, the result set contains an integer column that identifies the array element's cardinal number that the row represents. The new column is appended to the unnest derived table as its last column.

None

The following example illustrates how to use of the unnest operator with two arrays that have different cardinalities:

SELECT * FROM UNNEST( ARRAY(2,3,4), ARRAY(4,5,6) ) WITH ORDINALITY ASs
    DT(X,Y,Z);

The SQL statement returns the following result:

X	Y	Z
2	4	1
3	5	2
4	6	3

UNNEST array operator

Syntax

Arguments

Remarks

Privileges

See also

Example