Arrays | HEAVY.AI Documentation

HEAVY.AI supports arrays in dictionary-encoded text and number fields (TINYINT, SMALLINT, INTEGER, BIGINT, FLOAT, and DOUBLE). Data stored in arrays are not normalized. For example, {green,yellow} is not the same as {yellow,green}. As with many SQL-based services, OmniSci array indexes are 1-based.

HEAVY.AI supports NULL variable-length arrays for all integer and floating-point data types, including dictionary-encoded string arrays. For example, you can insert NULL into BIGINT[ ], DOUBLE[ ], or TEXT[ ] columns. HEAVY.AI supports NULL fixed-length arrays for all integer and floating-point data types, but not for dictionary-encoded string arrays. For example, you can insert NULL into BIGINT[2] DOUBLE[3], but not into TEXT[2] columns.

Expression	Description
`ArrayCol[n] ...`	Returns value(s) from specific location `n` in the array.
`UNNEST(ArrayCol)`	Extract the values in the array to a set of rows. Requires `GROUP BY`; projecting `UNNEST` is not currently supported.
`test = ANY ArrayCol`	`ANY` compares a scalar value with a single row or set of values in an array, returning results in which at least one item in the array matches. `ANY` must be preceded by a comparison operator.
`test = ALL ArrayCol`	`ALL` compares a scalar value with a single row or set of values in an array, returning results in which all records in the array field are compared to the scalar value. `ALL` must be preceded by a comparison operator.
`CARDINALITY()`	Returns the number of elements in an array. See example below.
`DOT_PRODUCT(array_col_1, array_col_2)`	Computes the dot product between two arrays of the same length, returning a scalar floating point value. If the input arrays (vectors) are of unit length, the computed dot product will represent the angular similarity of the two vectors.

CARDINALITY Example

heavysql> \d arr
CREATE TABLE arr (
sia SMALLINT[])
omnisql> select sia, CARDINALITY(sia) from arr;
sia        | EXPR$0
-----------+-------
NULL       | NULL
{}         | 0
{NULL}     | 1
{1}        | 1
{2,2}      | 2
{3,3,3}    | 3

Examples

The following examples show query results based on the table test_array created with the following statement:

1 CREATE TABLE test_array (name TEXT ENCODING DICT(32),colors TEXT[] ENCODING DICT(32), qty INT[]);

Example 1

Example 2

Example 3

Example 4

Example 5

Example 6

1 omnisql> SELECT * FROM test_array;
2 name|colors|qty
3 Banana|{green, yellow}|{1, 2}
4 Cherry|{red, black}|{1, 1}
5 Olive|{green, black}|{1, 0}
6 Onion|{red, white}|{1, 1}
7 Pepper|{red, green, yellow}|{1, 2, 3}
8 Radish|{red, white}|{}
9 Rutabaga|NULL|{}
10 Zucchini|{green, yellow}|{NULL}

The following queries use arrays in an INTEGER field: