ARRAYTABLE([ROW|ROWS] expression COLUMNS <COLUMN>, ...) AS name
COLUMN := name datatypeARRAYTABLE
The ARRAYTABLE function processes an array input to produce tabular output. The function itself defines what columns it projects. The ARRAYTABLE function is implicitly a nested table and can be used within FROM clauses.
Usage
Parameters
- expression
-
The array to process, which should be a java.sql.Array or java array value.
- ROW|ROWS
-
If ROW (the default) is specified, then only a single row is produced from the given array (typically a one dimensional array). If ROWS is specified, then multiple rows are produced. A multidimensional array is expected, and one row is produced for every non-null element of the outer array.
If the expression is null, no rows are produced.
Syntax rules
-
Columns names cannot contain duplicates.
Array table examples
-
As a nested table:
select x.* from (call source.invokeMDX('some query')) r, arraytable(r.tuple COLUMNS first string, second bigdecimal) xARRAYTABLE is effectively a shortcut for using the array_get function in a nested table.
For example, the following ARRAYTABLE function:
ARRAYTABLE(val COLUMNS col1 string, col2 integer) AS Xis the same as the following statement which uses an array_get function:
TABLE(SELECT cast(array_get(val, 1) AS string) AS col1, cast(array_get(val, 2) AS integer) AS col2) AS X