ARRAYTABLE

You are looking at an older version of the documentation. The latest version is found here.

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