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

SQL

ARRAYTABLE(expression COLUMNS <COLUMN>, ...) AS name
 
COLUMN := name datatype

Parameters

Parameter	Description
`expression`	The array to process, should be one of the following: a java.sql.Array; java array value; an array constructed using `ARRAY()`: CODE `ARRAY('1', 2.2, 3)`

Syntax Rules

The column names may not contain duplicates.

Examples

1. As a nested table:

CODE

SELECT x.* FROM (CALL source.invokeMDX('some query')) r, ARRAYTABLE(r.tuple COLUMNS first STRING, second BIGDECIMAL) x

2. Using ARRAY():

SQL

SELECT * FROM ARRAYTABLE(ARRAY('1', 2.2, 3) COLUMNS d STRING, e DOUBLE, f INTEGER) a

ARRAYTABLE is effectively a shortcut for using the ARRAY_GET function in a nested table. These two example calls are essentially the same:

CODE

ARRAYGET(val COLUMNS col1 STRING, col2 INTEGER) AS X

TABLE(SELECT CAST(ARRAY_GET(val, 1) AS STRING) AS col1, CAST(ARRAY_GET(val, 2) AS INTEGER) AS col2) AS X