---
title: Filtering
noindex: true
---

<Danger>
  **Legacy Docs:** This page describes our legacy API. It will be deprecated in
  a future version. Please use the [v2 API](/) where possible.
</Danger>

Adding filters to text search is as simple as using PostgreSQL's built-in `WHERE` clauses and operators.
For instance, the following query filters out results that do not meet `rating > 2`.

```sql
SELECT description, rating, category
FROM mock_items
WHERE description @@@ 'shoes' AND rating > 2;
```

## Filter Pushdown

### Non-Text Fields

While not required, filtering performance over non-text columns can be improved by including them in the BM25 index, and making sure they are configured as [fast](/legacy/indexing/fast-fields)
(which is already the default for non-text fields). When these columns are part of the index, `WHERE` clauses that reference them can be pushed down into the index scan itself. This can result in faster query execution over large datasets.

For example, if `rating` and `created_at` are frequently used in filters, they can be added to the BM25 index during index creation:

```sql
CREATE INDEX search_idx ON mock_items
USING bm25(id, description, rating, created_at)
WITH (key_field = 'id');
```

Filter pushdown is currently supported for the following combinations of types and operators:

| Operator                                   | Left Operand Type | Right Operand Type | Example                    |
| ------------------------------------------ | ----------------- | ------------------ | -------------------------- |
| `=`, `<`, `>`, `<=`, `>=`, `<>`, `BETWEEN` | `int2`            | `int2`             | `WHERE rating = 2`         |
|                                            | `int4`            | `int4`             |
|                                            | `int8`            | `int8`             |
|                                            | `int2`            | `int4`             |
|                                            | `int2`            | `int8`             |
|                                            | `int4`            | `int8`             |
|                                            | `float4`          | `float4`           |
|                                            | `float8`          | `float8`           |
|                                            | `float4`          | `float8`           |
|                                            | `date`            | `date`             |
|                                            | `time`            | `time`             |
|                                            | `timetz`          | `timetz`           |
|                                            | `timestamp`       | `timestamp`        |
|                                            | `timestamptz`     | `timestamptz`      |
|                                            | `uuid`            | `uuid`             |
| `=`                                        | `bool`            | `bool`             | `WHERE in_stock = true`    |
| `IN`, `ANY`, `ALL`                         | `bool`            | `bool[]`           | `WHERE rating IN (1,2,3)`  |
|                                            | `int2`            | `int2[]`           |
|                                            | `int4`            | `int4[]`           |
|                                            | `int8`            | `int8[]`           |
|                                            | `int2`            | `int4[]`           |
|                                            | `int2`            | `int8[]`           |
|                                            | `int4`            | `int8[]`           |
|                                            | `float4`          | `float4[]`         |
|                                            | `float8`          | `float8[]`         |
|                                            | `float4`          | `float8[]`         |
|                                            | `date`            | `date[]`           |
|                                            | `timetz`          | `timetz[]`         |
|                                            | `timestamp`       | `timestamp[]`      |
|                                            | `timestamptz`     | `timestamptz[]`    |
|                                            | `uuid`            | `uuid[]`           |
| `IS`, `IS NOT`                             | `bool`            | `bool`             | `WHERE in_stock IS true`   |
| `IS NULL`, `IS NOT NULL`                   | `bool`            |                    | `WHERE rating IS NOT NULL` |
|                                            | `int2`            |                    |
|                                            | `int4`            |                    |
|                                            | `int8`            |                    |
|                                            | `int2`            |                    |
|                                            | `int2`            |                    |
|                                            | `int4`            |                    |
|                                            | `float4`          |                    |
|                                            | `float8`          |                    |
|                                            | `float4`          |                    |
|                                            | `date`            |                    |
|                                            | `time`            |                    |
|                                            | `timetz`          |                    |
|                                            | `timestamp`       |                    |
|                                            | `timestamptz`     |                    |
|                                            | `uuid`            |                    |

### Text Fields

Suppose we have a text filter that looks for an exact string match like `category = 'Footwear'`:

```sql
SELECT description, rating, category
FROM mock_items
WHERE description @@@ 'shoes' AND category = 'Footwear';
```

To push down the `category = 'Footwear'` filter, `category` must be indexed using the `keyword` tokenizer:

```sql
CREATE INDEX search_idx ON mock_items
USING bm25(id, description, category)
WITH (key_field = 'id', text_fields = '{"category": {"tokenizer": {"type": "keyword"}}}');
```

Pushdown of set filters over text fields also requires the `keyword` tokenizer:

```sql
SELECT description, rating, category
FROM mock_items
WHERE description @@@ 'shoes' AND category IN ('Footwear', 'Apparel');
```
