# How to Filter Elements in ARRAY Fields

> A guide on how to filter rows based on whether a specific value exists within an ARRAY field in Holistics, using two different approaches.

Many modern data warehouses support ARRAY data types (e.g. Snowflake, PostgreSQL, BigQuery, Redshift). This guide describes a practical workaround in Holistics to filter rows based on whether a specific value exists inside an array field.

:::info
This guide uses PostgreSQL syntax for its examples, but the concepts can be adapted to other databases that support array functions.
:::

## Use case

You have an `orders` table with a column like `applied_promo_codes` (ARRAY of text). You want a dashboard filter such as "promo code contains SAVE10" and have Holistics return all orders that include `SAVE10` in that array.

![Orders model with applied_promo_codes array field and filter for SAVE10](https://media.holistics.io/96cb8ea3-filter-array-field-workaround.png)

## Approach 1: Simple contains filter

Convert the ARRAY into a text representation and use Holistics’ built-in **contains** text filter.

```aml
dimension applied_promo_codes {
  label: 'Applied Promo Codes'
  type: 'text'
  hidden: true
  definition: @sql {{ #SOURCE.applied_promo_codes }}::text;;
}
```

Then apply a text filter with **contains** on `applied_promo_codes`.

**Limitation:**
The **contains** operator performs substring matching. For example, filtering on `PROMO10` may also match `PROMO100`.
If you require exact element matching, use Approach 2.

For available text filter operators, see: https://docs.holistics.io/docs/filters/text-filters#available-operators

## Approach 2: Delimiter-based filter (avoid false positives)

To ensure exact matches, wrap each array element with delimiters (i.e. `,`, `#`) before converting it to text. This prevents partial matches such as `PROMO10` matching `PROMO100`.

```aml
dimension applied_promo_codes {
  label: 'Applied Promo Codes'
  type: 'text'
  hidden: true
  definition: @sql concat('#', array_to_string({{ #SOURCE.applied_promo_codes }}, '#'), '#');;
}
```

Create a parameter to capture the desired promo code value:

```aml
param array_value_param {
  label: 'Array Value'
  type: 'text'
}
```

Then filter using a delimiter-aware `LIKE` condition on `applied_promo_codes`, ensuring only exact promo code matches are returned.

```aql
model.applied_promo_codes LIKE concat('%#', model.array_value_param | first(), '#%')
```

Apply this as an AQL condition at the widget or metric level, and map a dashboard filter to `array_value_param`.

:::tip Accuracy tip
Pick a delimiter that is unlikely to appear in the element values (for example `#`). With the delimiter-based transform, the string becomes `#PROMO10#` and `#WELCOME#`, so searching for `#PROMO10#` avoids matching `PROMO100`.
:::