ARRAYTABLE

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:

ARRAYTABLE([ROW|ROWS] expression COLUMNS <COLUMN>, ...) AS name
COLUMN := name datatype

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) x

ARRAYTABLE 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 X

is 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

results matching ""

    No results matching ""