Array Functions
Applies to: ✅ Data Cloud SQL ✅ Tableau Hyper API
This section describes functions and operators that inspect and transform array
values. In this section, array(T)
represents an array type with element type T
.
These functions yield access to array elements or metadata. These operations are compatible with all nullable and non-nullable element types.
Signature | Description | Example |
---|---|---|
array(T)[int] → T | Returns the n-th element of the array (1-indexed) | (array[1,2,3])[1] → 1 |
array(T)[int:int] → T | Returns the subarray within the given bounds (1-indexed, inclusive) | (array[1,2,3])[2:3] → {2,3} |
array_length(array) → int | Returns the length of the array | array_length(array[1,2,3]) → 3 |
array_to_string(array, text [, text]) | Converts the array into a textual representation, with the given element separator and (optional) null indicator | `array_to_string(array[1,2,3], ';')` → `1;2;3` `array_to_string(array[3,2,1,null], '⏰', '🎉')` → `3⏰2⏰1⏰🎉` |
array_contains(array, value) | Checks if a given value is contained within the array. | `array_contains(array[1,3,4], 3)` → `true` `array_contains(array[1,3,4], 2)` → `false` |
array_position(array, value) | Returns the index of the first occurrence of value inside array . Comparisons are done by using IS NOT DISTINCT FROM semantics, so it’s possible to search for NULL . Returns NULL if the element isn’t found. | `array_position(array[1,3,4,3], 3)` → `2` `array_contains(array[1,3,4,3], 2)` → `NULL` |
array_positions(array, value) | Returns an array containing the indices of all occurrences of value inside array . Comparisons are done by using IS NOT DISTINCT FROM semantics, so it’s possible to search for NULL . NULL is returned only if the array is NULL . If the value isn’t found in the array, an empty array is returned. | `array_positions(array[1,3,4,3], 3)` → `[2,4]` `array_contains(array[1,3,4,3], 2)` → `[]` |
These functions produce new array values from existing ones. These operations are compatible with all nullable and non-nullable element types.
| Signature | Description | Example |
| :---------------------------------------------------------- | :---------- | :-------------------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------- | -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| `array_prepend(T,array(T))` → `array(T)` or T | | `array(T)` → `array(T)` | Inserts a value at the front of the array. If null
is prepended to an array with a non-nullable element type, the resulting element type is nullable. | `array_prepend(1, array[2,3,4])` → `array[1,2,3,4]` `array_prepend(null, [2,3,4])` → `array[null,2,3,4]` |
| `array_append(array(T), T)` → `array(T)`or array(T) | | `T` → `array(T)` | Inserts a value at the end of the array. If null
is appended to an array with a non-nullable element type, the resulting element type is nullable. | `array_append(array[1,2,3], 4)` → `array[1,2,3,4]` `array_append(array[1,2,3], null)` → `array[1,2,3,null]` |
| `array_cat(array(T), array(T))` → `array(T)`or array(T) | | `array(T)` → `array(T)` | Concatenates two arrays. The resulting element type is non-nullable if and only if both input element types are non-nullable. | `array_cat(array[1,2], array[3,4])` → `array[1,2,3,4]` `array_cat(array[1,null], array[3,4])` → `array[1,null,3,4]` `array_cat(array[1,2], array[null,4])` → `array[1,2,null,4]` |
Inner product functions operate on two input vectors, represented as arrays, and produce a scalar value of type double precision
.
Vector functions only apply to a set of arrays.
- The element type is
real
ordouble precision
. - Both arrays have the same element type but can have different element nullability.
- Both arrays are the same length.
If any of the input arrays contains a null
element, or is itself null
, the result of an inner product is null
. Passing arrays of different lengths or incompatible types results in an error.
In this table, signatures and examples are abbreviated for clarity. vec
represents a suitable array type. For example, array(real)
or array(double precision not null)
. It’s implied that both array arguments have the same element type. Similarly, the {1.0, 2.0, 3.0}
syntax in the example column represents a suitable array value. For example, '{1.0, 2.0, 3.0}'::array(real not null)
.
Signature | Description | Example |
---|---|---|
dot_product(vec, vec) → double precision | Computes the conventional dot product between two vectors. | `dot_product({1.0, 2.0, 3.0}, {-1.0, 2.0, -3.0})`→`-6.0` `dot_product({1.0, null, 3.0}, {-1.0, 2.0, -3.0})` → `null` |
cosine_similarity(vec, vec) → double precision | Computes cosine similarity between two vectors. | `cosine_similarity({1.0, 2.0, 3.0}, {-1.0, 2.0, -3.0})`→`-0.42857...` `cosine_similarity({1.0, 2.0, 3.0}, {null, 2.0, -3.0})` → `null` |
If possible, when computing inner products, use arrays with non-nullable element types such as array(real not null)
or array(double precision not null)
.
Vector search is a search technique used to find semantically similar items, called documents, in a given corpus.
For example, consider the problem of finding products similar to a search string in the product catalog of an ecommerce business. Let’s assume that the product catalog is loaded into a temporary table called products
with these columns.
description_vec
(type:array(real not null)
): a description of the product, represented as a suitable embedding vectorname
(type:text
): the name of the product
Further, let’s assume that the search string has been converted into a vector {1.1, -0.2, 0.7, -0.3}
by using the same embedding model as the description_vec
column.
Retrieving the top five most similar products can be expressed in SQL as: