> For clean Markdown of any page, append .md to the page URL.
> For a complete documentation index, see https://docs.nvidia.com/heavyai/llms.txt.
> For full documentation content, see https://docs.nvidia.com/heavyai/llms-full.txt.
> For AI client integration (Claude Code, Cursor, etc.), connect to the MCP server at https://docs.nvidia.com/heavyai/_mcp/server.

# 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. 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:

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

```sql
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}
```

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

```sql
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
```

```sql
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
```

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

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

The following queries use arrays in an INTEGER field:

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

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

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

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

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