UNNEST

UNNEST(anyarray) RETURNS SETOF anyelement

• anyarray: UNNEST(anyarray) takes as parameter an array. This parameter represents the array that will be disassembled into a set of rows.
• returns setof anyelement: This is the syntax specifying the type of return value from the UNNEST() function. It indicates that the function will return a set of elements whose datatype is the same as the datatype of the elements of the array parameter. The number of rows returned corresponds to the number of elements in the array.

Example

CREATE TABLE temp_table (numbers INT[]);
INSERT INTO temp_table VALUES (ARRAY[1,2]),(ARRAY[3,4]);

SELECT UNNEST(numbers) FROM temp_table;

Output

 unnest
--------
      1
      2
      3
      4

Explanation

In the above example, a temporary table temp_table is created with one column numbers of type INT[]. The UNNEST function is then used on that column in a SELECT statement. The UNNEST function takes an array and returns a table with a single column containing all the elements of the array as separate rows. Thus, the output contains individual elements of the array in separate rows.

UNNEST(nested_table_collection_expression)

• nested_table_collection_expression: This refers to a system-generated or user-defined type, or a type method that returns a nested table. In other words, it’s a table, stored within a table, which can then be extracted using UNNEST.

Example

This is an example SQL query using UNNEST:

CREATE TYPE number_table AS TABLE OF NUMBER;

DECLARE
  my_table number_table;
BEGIN
  my_table := number_table(1, 2, 3);

  SELECT column_value
  FROM TABLE(CAST(my_table AS number_table));
END;

Output

The output of the above SQL query will be:

COLUMN_VALUE
------------
1
2
3

Explanation

The above code demonstrates the use of an Oracle collection, number_table. The collection is an array of numbers. The UNNEST operation is performed implicitly in Oracle when TABLE() function is used on this collection. It results in the elements of the collection being returned as separate rows in the output.