---
schema_version: "1"
id: ba16c50e43a2601c9da6e44bfabcf18846896f89b0ca1feed17f8f82cb7e0528
title: Array Functions
collection: developer
version: current
locale: en-us
portal_url: https://docs.salesforce.com/developer/current/en-us/data-cloud/data-cloud-query-guide/references/data-cloud-sql-reference/scalar-func/array-func/arrays.md
canonical_url: https://developer.salesforce.com/docs/data/data-cloud-query-guide/references/dc-sql-reference/arrays.html
source_site: developer.salesforce.com
content_type: documentation
content_hash: a9a361b9e7f0851ee1eea1e8b83aed2825fe7a4e273dc069b7b50dd6a5a5e4b2
products:
  - Data 360 Query Guide
  - Data 360 Developer Documentation
---
# Array Functions

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

Functions and operators for inspecting, accessing, and transforming array values. Use array functions to:

- **Access and inspect** array elements by index or search for values.
- **Measure and analyze** arrays by checking length and finding positions.
- **Modify and transform** arrays by adding, removing, or deduplicating elements.
- **Perform vector operations** for similarity searches on embeddings.

## Considerations

- **1-based indexing**: Array indices start at 1, not 0.
- **Element types**: Many functions require matching element types between arrays.
- **NULL handling**: NULL elements are preserved unless explicitly removed with

For more information about supported types, see [Array Type](https://developer.salesforce.com/docs/data/data-cloud-query-guide/datatype/array.md).

## Access and Inspection

Access array elements and check for content:

- [Array Subscript Access](https://developer.salesforce.com/docs/data/data-cloud-query-guide/references/dc-sql-reference/array-subscript.md) - Get the element at the index.
- [ARRAY_LENGTH](https://developer.salesforce.com/docs/data/data-cloud-query-guide/references/dc-sql-reference/array-length.md) - Count the total number of elements.
- [ARRAY_CONTAINS](https://developer.salesforce.com/docs/data/data-cloud-query-guide/references/dc-sql-reference/array-contains.md) - Check whether a value exists.
- [ARRAY_CONTAINS_ANY](https://developer.salesforce.com/docs/data/data-cloud-query-guide/references/dc-sql-reference/array-contains-any.md) - Check for the overlap between arrays.
- [ARRAY_POSITION](https://developer.salesforce.com/docs/data/data-cloud-query-guide/references/dc-sql-reference/array-position.md) - Find the first occurrence of the value.
- [ARRAY_POSITIONS](https://developer.salesforce.com/docs/data/data-cloud-query-guide/references/dc-sql-reference/array-positions.md) - Find all occurrences of the value.
- [ARRAY_TO_STRING](https://developer.salesforce.com/docs/data/data-cloud-query-guide/references/dc-sql-reference/array-to-string.md) - Convert an array to a delimited string.

## Array Building

Create and build arrays:

- [Array Constructor](https://developer.salesforce.com/docs/data/data-cloud-query-guide/references/dc-sql-reference/array-constructor.md) - Create an array from values.
- [ARRAY_AGG](https://developer.salesforce.com/docs/data/data-cloud-query-guide/aggregate-func/array-agg.md) - Collect values from a group into an array.
- [ARRAY_PREPEND](https://developer.salesforce.com/docs/data/data-cloud-query-guide/references/dc-sql-reference/array-prepend.md) - Insert a value at the front of an array.
- [ARRAY_APPEND](https://developer.salesforce.com/docs/data/data-cloud-query-guide/references/dc-sql-reference/array-append.md) - Insert a value at end of an array.
- [ARRAY_CAT](https://developer.salesforce.com/docs/data/data-cloud-query-guide/references/dc-sql-reference/array-cat.md) - Concatenate two arrays.
- [ARRAY_GENERATE_SERIES](https://developer.salesforce.com/docs/data/data-cloud-query-guide/references/dc-sql-reference/array-generate-series.md) - Generate a sequence array.

## Array Transformation

Sort, filter, and transform arrays:

- [ARRAY_TRANSFORM](https://developer.salesforce.com/docs/data/data-cloud-query-guide/references/dc-sql-reference/array-transform.md) - Apply a lambda function to each element.
- [ARRAY_FILTER](https://developer.salesforce.com/docs/data/data-cloud-query-guide/references/dc-sql-reference/array-filter.md) - Keep only elements matching a condition.
- [ARRAY_SORT](https://developer.salesforce.com/docs/data/data-cloud-query-guide/references/dc-sql-reference/array-sort.md) - Sort elements in ascending or descending order.
- [ARRAY_DISTINCT](https://developer.salesforce.com/docs/data/data-cloud-query-guide/references/dc-sql-reference/array-distinct.md) - Remove duplicate elements.
- [ARRAY_REMOVE](https://developer.salesforce.com/docs/data/data-cloud-query-guide/references/dc-sql-reference/array-remove.md) - Remove all occurrences of a value.
- [ARRAY_REMOVE_AT](https://developer.salesforce.com/docs/data/data-cloud-query-guide/references/dc-sql-reference/array-remove-at.md) - Remove the element at the defined position.
- [ARRAY_COMPACT](https://developer.salesforce.com/docs/data/data-cloud-query-guide/references/dc-sql-reference/array-compact.md) - Remove all NULL elements.
- [TRIM_ARRAY](https://developer.salesforce.com/docs/data/data-cloud-query-guide/references/dc-sql-reference/trim-array.md) - Remove the last `n` elements.

## Array Expansion

Expand an array into a set of rows:

- [`unnest`](https://developer.salesforce.com/docs/data/data-cloud-query-guide/set-returning-func/unnest.md) - Expand array elements into a set of rows.

## Vector and Similarity

Perform vector operations for similarity searches on embeddings:

- [DOT_PRODUCT](https://developer.salesforce.com/docs/data/data-cloud-query-guide/references/dc-sql-reference/dot-product.md) - Compute the scalar product of vectors.
- [COSINE_SIMILARITY](https://developer.salesforce.com/docs/data/data-cloud-query-guide/references/dc-sql-reference/cosine-similarity.md) - Measure directional similarity between arrays.
- [BINARY_QUANTIZE](https://developer.salesforce.com/docs/data/data-cloud-query-guide/references/dc-sql-reference/binary-quantize.md) - Compress a vector to binary values.
- [HAMMING_SIMILARITY](https://developer.salesforce.com/docs/data/data-cloud-query-guide/references/dc-sql-reference/hamming-similarity.md) - Compare binary vectors.

## Examples

### Filtering by Array Content

Find products with specific tags.

```sql
SELECT product_name
FROM products
WHERE array_contains(tags, 'sale');
```

Find products matching any search tag.

```sql
SELECT product_name
FROM products
WHERE array_contains_any(tags, array['new', 'featured', 'popular']);
```

### Array Manipulation

Build a complete list by combining arrays.

```sql
SELECT array_cat(primary_categories, secondary_categories) AS all_categories
FROM products;
```

Clean up arrays by removing nulls and duplicates.

```sql
SELECT array_distinct(array_compact(values)) AS clean_values
FROM data;
```

### Transforming Array Elements

Double every element using a lambda expression.

```sql
SELECT array_transform(array[1, 2, 3, 4], lambda x: x * 2) AS doubled;
-- Returns [2, 4, 6, 8]
```

Filter to keep only values above a threshold.

```sql
SELECT array_filter(array[10, 25, 5, 30, 15], lambda x: x > 20) AS large;
-- Returns [25, 30]
```

### Vector Search

Find top five most similar products using embeddings

```sql
SELECT
    name,
    dot_product('[0.1, -0.2, 0.7, -0.3]'::REAL[], description_vec) AS score
FROM products
ORDER BY score DESC
LIMIT 5;
```

## Related Documentation

- [Array Type](https://developer.salesforce.com/docs/data/data-cloud-query-guide/datatype/array.md)
- [Lambda Expressions](https://developer.salesforce.com/docs/data/data-cloud-query-guide/lambda-expressions.md)
- [Aggregate Functions](https://developer.salesforce.com/docs/data/data-cloud-query-guide/aggregate-func/aggregate.md)