Explanation: The UNNEST operator is an SQL Script element that transforms a set of arrays into an intermediate table variable. The UNNEST operator takes one or more array expressions as input and returns a table variable with one or more columns, each corresponding to an array expression. The table variable has as many rows as the maximum length of the input arrays, and the values in each column are taken from the corresponding array elements. If an input array has fewer elements than the maximum length, the missing values are padded with NULLs. The UNNEST operator can be used in the FROM clause of a SELECT statement, or in the INTO clause of a SET statement.
For example, suppose you have two arrays A and B, where A = [1, 2, 3] and B = [4, 5]. You can use the UNNEST operator to transform them into a table variable T as follows:
SET T = UNNEST (A, B);
The result is:
Table
A
B
1
4
2
5
3
NULL
You can also use the UNNEST operator to transform an array of complex types, such as structs or tables, into a table variable with multiple columns. For example, suppose you have an array C of struct type, where C = [{x: 1, y: 2}, {x: 3, y: 4}]. You can use the UNNEST operator to transform it into a table variable U as follows:
SET U = UNNEST (C.x, C.y);
The result is:
Table
C.x
C.y
1
2
3
4
The following SQL Script elements are not used to transform a set of arrays into an intermediate table variable, but for other purposes:
- OSET: This is a data type that represents an ordered set, which is a collection of distinct values that preserves the order of insertion. OSETs can be used to store and manipulate ordered data, such as ranking or sorting results. OSETs can be created by using the OSET constructor or the OSET_AGG aggregate function.
- ARRAY: This is a data type that represents an array, which is a collection of values that can have duplicates and are accessed by their position. Arrays can be used to store and manipulate complex or multi-dimensional data, such as matrices or tensors. Arrays can be created by using the ARRAY constructor or the ARRAY_AGG aggregate function.
- UNSET: This is not a valid SQL Script element, but a possible typo for UNNEST.
References:
- [SAP HANA Platform Documentation], SAP HANA SQL and System Views Reference, Chapter 2: SQL Data Types and Literals, Section 2.2: Collection Types, Subsection 2.2.2: Array Type, pp. 36-38.
- [SAP HANA Platform Documentation], SAP HANA SQL and System Views Reference, Chapter 2: SQL Data Types and Literals, Section 2.2: Collection Types, Subsection 2.2.3: Ordered Set Type, pp. 38-40.
- [SAP HANA Platform Documentation], SAP HANA SQL and System Views Reference, Chapter 3: SQL Expressions, Section 3.6: Collection Expressions, Subsection 3.6.2: UNNEST Operator, pp. 69-70.
