Arrays
Last updated
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 |
---|---|
The following examples show query results based on the table test_array
created with the following statement:
The following queries use arrays in an INTEGER field:
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: