Array Functions

Applies to: ✅ Data 360 SQL | ✅ Tableau Hyper API

Use these functions and operators to inspect, access, and transform array values. In the function signatures, array(T) represents an array with an element type T. For more information about supported types, see Array Type. All array functions assume the array is indexed on one.

These functions provide access to array elements and metadata or check for content. They are compatible with all element types.

Signature	Description	Example
`array(T)[int]` → `T`	Returns the element at the defined integer value.	`(array[1, 2, 3])[1]` → `1`
`array(T)[int:int]` → `T`	Returns the subarray within the given bounds.	`(array[1, 2, 3])[2:3]` → `{2, 3}`
`array_length(array)` → `int`	Returns the total number of elements in the array.	`array_length(array[1, 2, 3])` → `3`
`array_contains(array, value)` → `boolean`	Checks whether a specified value is present.	`array_contains(array[1,3,4], 3)` → `true` `array_contains(array[1,3,4], 2)` → `false`
`array_contains_any(array1, array2)` → `boolean`	Checks whether there is any overlap between two arrays.	`array_contains_any(array[1, 2], array[2, 3])` → `true`
`array_position(array, value)` → `int`	Returns the first occurrence and the specified value. Returns null if not found.	`array_position(array[1,3,4,3], 3)` → `2 array_position(array[1,3,4,3], 2)` → `null`
`array_positions(array, value)` → `array(int)`	Returns an array of the indices of all occurrences. Returns an empty array if the value isn’t found.	`array_positions(array[1,3,4,3], 3)` → `[2,4] array_positions(array[1,3,4,3], 2)` → `[]`
`array_to_string(array, text [, text])`	Converts the array to a string using a separator and an optional null indicator.	`array_to_string(array[1,2,3], ';')` → `1;2;3` `array_to_string(array[3,2,1,null], '⏰', '🎉')` → `3⏰2⏰1⏰🎉`

These functions return new array values based on appending, removing, or generating elements.

Function	Description	Example
`array_prepend(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)`	Inserts a value at the end of the array. If you append 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)`	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]`
`array_remove(array, element)` → `array(T)`	Returns a new array with all instances of a specified element removed.	`array_remove(array[1, 2, 1, 3], 1)` → `array[2, 3]`
`array_remove_at(array, position)` → `array(T)`	Returns an array with the element at a specified position removed.	`array_remove_at(array[1, 2, 3], 2)` → `array[1, 3]`
`array_distinct(array)` → `array`	Returns an array with duplicate elements removed, preserving the first occurrence of each duplicate element.	`array_distinct(array[1, 2, 1, 3])` → `array[1, 2, 3]`
`array_compact(array)` → `array`	Returns an array with all null elements removed.	`array_compact(array[1, null, 2])` → `array[1, 2]`
`trim_array(array, count)` → `array`	Trims an array by removing the last `count` elements.	`trim_array(array[1, 2, 3, 4], 2)` → `array[1, 2]`
`array_generate_series(start, stop, step)` → `array`	Creates an array of sequential values within a range. The range begins at the defined `start` value, ends at the `stop` value, and increments by the `step` value. `step` is optional and defaults to `1`.	`array_generate_series(1, 5, 1)` → `array[1, 2, 3, 4, 5]`

You can use these functions as a building block in larger queries to perform similarity searches on vector embeddings.

These functions operate on vectors represented as arrays (array(real) or array(double precision)). Both input arrays must be of the same element type and length. If any element is null in the input vectors, the result is null.

For optimal performance with these functions, use array definitions that enforce non-nullable elements. For example, array(real not null) or array(double precision not null).

Function	Description	Example
`dot_product(arr, arr)` → `double precision`	Computes the conventional dot product (scalar product) of two vectors, a standard similarity measure.	`dot_product([1, 2, 3], [-1, 2, -3])` → `-6.0`
`cosine_similarity(arr, arr)` → `double precision`	Computes the cosine similarity between two vectors, ranging from -1 (opposite) to 1 (identical).	`cosine_similarity([1, 2, 3], [-1, 2, -3])` → `-0.42857…`
`binary_quantize(arr)` → `bytea`	Encodes each array element into a single bit (1 if greater than 0), compressing the vector by ~32x.	`binary_quantize(ARRAY[-5, -3, 2, 6])` → `'0011'`
`hamming_similarity(bytea, bytea)` → `double precision`	Calculates the similarity between two quantized (binary) embeddings using the normalized Hamming distance. This provides a fast, approximate similarity score.	`hamming_similarity('1011', '0011')` → `0.75`

Vector search is a search technique used to find semantically similar items, called documents, in a given set of values.

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

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: