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

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

  <Tab title="Example 2">
    ```sql
    omnisql> SELECT UNNEST(colors) AS c FROM test_array;
    Exception: UNNEST not supported in the projection list yet.
    ```
  </Tab>

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

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

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

  <Tab title="Example 6">
    ```sql
    omnisql> SELECT * FROM test_array WHERE colors IS NULL;
    name|colors|qty
    Rutabaga|NULL|{}
    ```
  </Tab>
</Tabs>

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

  <Tab title="Example 2">
    ```sql
    omnisql> SELECT name, qty FROM test_array WHERE 15< ALL qty;
    No rows returned.
    ```
  </Tab>

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

  <Tab title="Example 4">
    ```sql
    omnisql> SELECT COUNT(*) FROM test_array WHERE qty IS NOT NULL;
    EXPR$0
    8
    ```
  </Tab>

  <Tab title="Example 5">
    ```sql
    omnisql> SELECT COUNT(*) FROM test_array WHERE CARDINALITY(qty)<0;
    EXPR$0
    6
    ```
  </Tab>
</Tabs>