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

<table><thead><tr><th>Expression</th><th>Description</th></tr></thead><tbody><tr><td><code>ArrayCol[n] ...</code></td><td>Returns value(s) from specific location <code>n</code> in the array.</td></tr><tr><td><code>UNNEST(ArrayCol)</code></td><td>Extract the values in the array to a set of rows. Requires <code>GROUP BY</code>; projecting <code>UNNEST</code> is not currently supported.</td></tr><tr><td><code>test = ANY ArrayCol</code></td><td><code>ANY</code> 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. <code>ANY</code> must be preceded by a comparison operator.</td></tr><tr><td><code>test = ALL ArrayCol</code></td><td><code>ALL</code> 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. <code>ALL</code> must be preceded by a comparison operator.</td></tr><tr><td><code>CARDINALITY()</code></td><td><p>Returns the number of elements in an array. For example:</p><pre><code>heavysql> \d arr
</code></pre></td></tr><tr><td>CREATE TABLE arr (</td><td></td></tr><tr><td>sia SMALLINT[])</td><td></td></tr><tr><td>omnisql> select sia, CARDINALITY(sia) from arr;</td><td></td></tr><tr><td>sia</td><td>EXPR$0</td></tr><tr><td>NULL</td><td>NULL</td></tr><tr><td>{}</td><td>0</td></tr><tr><td>{NULL}</td><td>1</td></tr><tr><td>{1}</td><td>1</td></tr><tr><td>{2,2}</td><td>2</td></tr><tr><td>{3,3,3}</td><td>3</td></tr><tr><td></td><td></td></tr><tr><td><code>DOT_PRODUCT(array_col_1, array_col_2)</code></td><td>Computes the dot product between two arrays of the same length, returning a scalar floating point value.<br><br>If the input arrays (vectors) are of unit length, the computed dot product will represent the angular similarity of the two vectors.</td></tr></tbody></table>

### 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[]);
```

{% tabs %}
{% tab title="Example 1" %}

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

{% endtab %}

{% tab title="Example 2" %}

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

{% endtab %}

{% tab title="Example 3" %}

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

{% endtab %}

{% tab title="Example 4" %}

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

{% endtab %}

{% tab title="Example 5" %}

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

{% endtab %}

{% tab title="Example 6" %}

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

{% endtab %}
{% endtabs %}

The following queries use arrays in an INTEGER field:

{% tabs %}
{% tab title="Example 1" %}

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

{% endtab %}

{% tab title="Example 2" %}

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

{% endtab %}

{% tab title="Example 3" %}

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

{% endtab %}

{% tab title="Example 4" %}

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

{% endtab %}

{% tab title="Example 5" %}

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

{% endtab %}
{% endtabs %}