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 may be correlated to preceding FROM clause entries.
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 will be produced from the given array (typically a one dimensional array). If ROWS is specified, then multiple rows will be produces. A multidimensional array will be expected, and one row will be produced for every non-null element of the outer array.
If the expression is null, no rows are produced.
Syntax Rules:
-
The columns names must be not contain duplicates.
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 Miscellaneous Functions#array_get function in a nested table. For example
ARRAYTABLE(val COLUMNS col1 string, col2 integer) AS Xis the same as
TABLE(SELECT cast(array_get(val, 1) AS string) AS col1, cast(array_get(val, 2) AS integer) AS col2) AS X