15.00 - UNNEST - Teradata Database

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: A reference to an ARRAY column An ARRAY constructor expression. See “ARRAY Constructor Expression” on page 195. A UDF expression A UDM expression

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:

out_key is the optional key value passed to UNNEST. In this example, it corresponds to the value of the int_key column in the tt2 table, so it identifies the array values in the results that come from each row of the tt2 table.

pos is the position of an element within the array. It is included in the results because the example uses the WITH ORDINALITY clause.

val is the value of the element in the array.

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            ?