UNNEST
Purpose
A table function for use with ARRAY data types that allows arrays to be converted into column tables.
Syntax
where:
Syntax element… |
Specifies… |
TD_SYSFNLIB |
the name of the database where the function is located. |
key_expr |
an optional expression that evaluates to a Teradata data type, except for ARRAY, UDT, Period data type, BLOB, or CLOB. |
array_expr |
one of the following: |
ANSI Compliance
UNNEST for a one-dimensional (1-D) ARRAY data type is ANSI SQL:2011 compliant. However, the optional key_expr parameter is a Teradata extension to the ANSI SQL:2011 standard.
UNNEST for a multidimensional (n-D) ARRAY data type is a Teradata extension to the ANSI SQL:2011 standard.
Argument Types
UNNEST accepts either 1 or 2 arguments. The optional key value argument allows for grouping the rows produced in the result table. This is especially helpful when using UNNEST as a complement of the ARRAY_AGG function, because ARRAY_AGG may be invoked with the aggregate GROUP BY clause to produce multiple ARRAY values based on the grouping.
The array_expr argument is an expression that evaluates to an ARRAY data type that is currently defined in the system.
Result Type and Value
UNNEST returns a table that contains one, two, or three columns. The first output column corresponds to the optional key value, if it was specified. The next output column has the same data type as the element type of the ARRAY type that was passed in. If the optional WITH ORDINALITY clause was specified, then UNNEST generates an additional integer column that contains the position associated with each element.
The table is populated with one row for each element of the ARRAY value passed to UNNEST. For an n-D ARRAY input value, the column containing the element values from the input n-D ARRAY are output in row-major order.
Usage Notes
UNNEST is a table function; therefore, it can only appear in the FROM clause of an SQL SELECT statement.
If the ARRAY expression argument evaluates to NULL, then no rows will be returned in the result table. If the ARRAY expression argument is not NULL but has one or more elements that are not present, then the column value for the array element in the corresponding result row will be NULL.
Example
This example calls UNNEST, passing the optional key value argument and using the optional WITH ORDINALITY clause, using a column of 1-D ARRAY type myarray. The example returns 20 rows, one for each value stored in the myarray array for each row of the tt2 table. The result has these three columns:
CREATE TYPE myarray AS INTEGER ARRAY[10];
CREATE TABLE tt2(
pkey INTEGER,
int_key INTEGER,
vc_key VARCHAR(20),
myarr myarray);
INS INTO tt2 VALUES(0, 0, 'item 0', NEW myarray(10, 20, 30, 40, 50));
INS INTO tt2 VALUES(1, 1, 'item 1', NEW myarray(11, 21, 31, 41, 51));
INS INTO tt2 VALUES(2, 2, 'item 2', NEW myarray(12, 22, 32, 42, 52));
INS INTO tt2 VALUES(3, 3, 'item 3', NEW myarray(NULL, 23, 33, 43, NULL));
INS INTO tt2 VALUES(4, 4, 'item 4', NULL);
SELECT out_key, tf.pos, tf.val
FROM tt2,
TABLE (UNNEST(tt2.int_key, tt2.myarr) WITH ORDINALITY) AS tf(out_key, val, pos) WHERE tt2.int_key = tf.out_key ORDER BY 1,2;
*** Query completed. 20 rows found. 3 columns returned.
*** Total elapsed time was 1 second.
out_key pos val
----------- ----------- -----------
0 1 10
0 2 20
0 3 30
0 4 40
0 5 50
1 1 11
1 2 21
1 3 31
1 4 41
1 5 51
2 1 12
2 2 22
2 3 32
2 4 42
2 5 52
3 1 ?
3 2 23
3 3 33
3 4 43
3 5 ?