> ## Documentation Index
> Fetch the complete documentation index at: https://docs.paradedb.com/llms.txt
> Use this file to discover all available pages before exploring further.

# Filtering

> Filter search results based on metadata from other fields

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`.

<CodeGroup>
  ```sql SQL theme={null}
  SELECT description, rating, category
  FROM mock_items
  WHERE description ||| 'running shoes' AND rating > 2;
  ```

  ```ts Drizzle theme={null}
  import { and, gt } from "drizzle-orm";
  import { search } from "@paradedb/drizzle-paradedb";

  await db
    .select({
      description: mockItems.description,
      rating: mockItems.rating,
      category: mockItems.category,
    })
    .from(mockItems)
    .where(
      and(
        search.matchAny(mockItems.description, "running shoes"),
        gt(mockItems.rating, 2),
      ),
    );
  ```

  ```python Django theme={null}
  from paradedb import MatchAny, ParadeDB

  MockItem.objects.filter(
      description=ParadeDB(MatchAny('running shoes')),
      rating__gt=2
  ).values('description', 'rating', 'category')
  ```

  ```python SQLAlchemy theme={null}
  from sqlalchemy import select
  from sqlalchemy.orm import Session
  from paradedb.sqlalchemy import search

  stmt = (
      select(MockItem.description, MockItem.rating, MockItem.category)
      .where(search.match_any(MockItem.description, "running shoes"), MockItem.rating > 2)
  )

  with Session(engine) as session:
      session.execute(stmt).all()
  ```

  ```ruby Rails theme={null}
  MockItem.search(:description)
          .matching_any("running shoes")
          .where(rating: 3..)
          .select(:description, :rating, :category)
  ```

  ```cs EF Core theme={null}
  await dbContext
      .MockItems.Where(item =>
          EF.Functions.MatchAny(item.Description, "running shoes") && item.Rating > 2
      )
      .Select(item => new { item.Description, item.Rating, item.Category })
      .ToListAsync();
  ```
</CodeGroup>

## Filter Pushdown

### Non-Text Fields

While not required, filtering performance over non-text columns can be improved by including them in the BM25 index.
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 theme={null}
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`           |                            |
|                                            | `numeric`         | `numeric`          | `WHERE price = 99.99`      |
|                                            | `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'`:

<CodeGroup>
  ```sql SQL theme={null}
  SELECT description, rating, category
  FROM mock_items
  WHERE description @@@ 'shoes' AND category = 'Footwear';
  ```

  ```ts Drizzle theme={null}
  import { and, eq } from "drizzle-orm";
  import { search } from "@paradedb/drizzle-paradedb";

  await db
    .select({
      description: mockItems.description,
      rating: mockItems.rating,
      category: mockItems.category,
    })
    .from(mockItems)
    .where(
      and(
        search.term(mockItems.description, "shoes"),
        eq(mockItems.category, "Footwear"),
      ),
    );
  ```

  ```python Django theme={null}
  from paradedb import ParadeDB, Term

  MockItem.objects.filter(
      description=ParadeDB(Term('shoes')),
      category='Footwear'
  ).values('description', 'rating', 'category')
  ```

  ```python SQLAlchemy theme={null}
  from sqlalchemy import select
  from sqlalchemy.orm import Session
  from paradedb.sqlalchemy import search

  stmt = (
      select(MockItem.description, MockItem.rating, MockItem.category)
      .where(search.term(MockItem.description, "shoes"), MockItem.category == "Footwear")
  )

  with Session(engine) as session:
      session.execute(stmt).all()
  ```

  ```ruby Rails theme={null}
  MockItem.search(:description)
          .term("shoes")
          .where(category: "Footwear")
          .select(:description, :rating, :category)
  ```

  ```cs EF Core theme={null}
  await dbContext
      .MockItems.Where(item =>
          EF.Functions.Term(item.Description, "shoes") && item.Category == "Footwear"
      )
      .Select(item => new { item.Description, item.Rating, item.Category })
      .ToListAsync();
  ```
</CodeGroup>

To push down the `category = 'Footwear'` filter, `category` must be indexed using the [literal](/documentation/tokenizers/available-tokenizers/literal) tokenizer:

```sql theme={null}
CREATE INDEX search_idx ON mock_items
USING bm25(id, description, (category::pdb.literal))
WITH (key_field = 'id');
```

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

<CodeGroup>
  ```sql SQL theme={null}
  SELECT description, rating, category
  FROM mock_items
  WHERE description @@@ 'shoes' AND category IN ('Footwear', 'Apparel');
  ```

  ```ts Drizzle theme={null}
  import { and, inArray } from "drizzle-orm";
  import { search } from "@paradedb/drizzle-paradedb";

  await db
    .select({
      description: mockItems.description,
      rating: mockItems.rating,
      category: mockItems.category,
    })
    .from(mockItems)
    .where(
      and(
        search.term(mockItems.description, "shoes"),
        inArray(mockItems.category, ["Footwear", "Apparel"]),
      ),
    );
  ```

  ```python Django theme={null}
  from paradedb import ParadeDB, Term

  MockItem.objects.filter(
      description=ParadeDB(Term('shoes')),
      category__in=['Footwear', 'Apparel']
  ).values('description', 'rating', 'category')
  ```

  ```python SQLAlchemy theme={null}
  from sqlalchemy import select
  from sqlalchemy.orm import Session
  from paradedb.sqlalchemy import search

  stmt = (
      select(MockItem.description, MockItem.rating, MockItem.category)
      .where(search.term(MockItem.description, "shoes"), MockItem.category.in_(["Footwear", "Apparel"]))
  )

  with Session(engine) as session:
      session.execute(stmt).all()
  ```

  ```ruby Rails theme={null}
  MockItem.search(:description)
          .term("shoes")
          .where(category: ["Footwear", "Apparel"])
          .select(:description, :rating, :category)
  ```

  ```cs EF Core theme={null}
  await dbContext
      .MockItems.Where(item =>
          EF.Functions.Term(item.Description, "shoes")
          && new[] { "Footwear", "Apparel" }.Contains(item.Category)
      )
      .Select(item => new { item.Description, item.Rating, item.Category })
      .ToListAsync();
  ```
</CodeGroup>