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.

SignatureDescriptionExample
array(T)[int]TReturns the n-th element of the array (1-indexed)(array[1,2,3])[1]1
array(T)[int:int]TReturns the subarray within the given bounds (1-indexed, inclusive)(array[1,2,3])[2:3]{2,3}
array_length(array)intReturns the length of the arrayarray_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 or double 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).

SignatureDescriptionExample
dot_product(vec, vec)double precisionComputes 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 precisionComputes 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 vector
  • name (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: