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 and are compatible with all element types.

Signature	Description	Example
`array(T)[int]` → `T`	Returns the n-th element of the array (one-indexed)	`(array[1,2,3])[1]` → `1`
`array(T)[int:int]` → `T`	Returns the subarray within the given bounds (one-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 the array contains a given value.	`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 use `IS NOT DISTINCT FROM` semantics, so you can 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 use `IS NOT DISTINCT FROM` semantics, so you can search for `NULL`. Returns `NULL` if the array is `NULL`. If the value isn’t found in the array, `array_positions` returns an empty array.	`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 and are compatible with all 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 you prepend `NULL` 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 you prepend `NULL` 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) \|\| T` → `array(T)`	Concatenates two arrays. The resulting element type is non-nullable 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 with these constraints:

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.

For clarity, this table abbreviates signatures and examples. 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. This example assumes that the product catalog is in 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, the example assumes that the search string is a vector [1.1, -0.2, 0.7, -0.3] by using the same embedding model as the description_vec column.

This SQL statement retrieves the top five most similar products: