Arrays

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. For 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
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.

Examples

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

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

Example 1

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

Example 2

omnisql> SELECT UNNEST(colors) AS c FROM test_array;
Exception: UNNEST not supported in the projection list yet.

Example 3

omnisql> SELECT UNNEST(colors) AS c, count(*) FROM test_array group by c;
c|EXPR$1
green|4
yellow|3
red|4
black|2
white|2

Example 4

omnisql> SELECT name, colors [2] FROM test_array;
name|EXPR$1
Banana|yellow
Cherry|black
Olive|black
Onion|white
Pepper|green
Radish|white
Rutabaga|NULL
Zucchini|yellow

Example 5

omnisql> SELECT name, colors FROM test_array WHERE colors[1]='green';
name|colors
Banana|{green, yellow}
Olive|{green, black}
Zucchini|{green, yellow}

Example 6

omnisql> SELECT * FROM test_array WHERE colors IS NULL;
name|colors|qty
Rutabaga|NULL|{}

The following queries use arrays in an INTEGER field:

Example 1

omnisql> SELECT name, qty FROM test_array WHERE qty[2] >1;
name|qty
Banana|{1, 2}
Pepper|{1, 2, 3}

Example 2

omnisql> SELECT name, qty FROM test_array WHERE 15< ALL qty;
No rows returned.

Example 3

omnisql> SELECT name, qty FROM test_array WHERE 2 = ANY qty;
name|qty
Banana|{1, 2}
Pepper|{1, 2, 3}

Example 4

omnisql> SELECT COUNT(*) FROM test_array WHERE qty IS NOT NULL;
EXPR$0
8

Example 5

omnisql> SELECT COUNT(*) FROM test_array WHERE CARDINALITY(qty)<0;
EXPR$0
6