---
title: Filtering
description: Filter operators for the where clause, varying by scalar type
icon: filter
---

## Basic Filtering

```ts
const users = await client.user.findMany({
  where: {
    role: "ADMIN",              // Exact match
    active: true,               // Boolean
    age: { gte: 18 },           // Comparison
    name: { contains: "alice" }, // String search
  },
});
```

## Common Operators

All scalar types support:

| Operator | Description |
|----------|-------------|
| `equals` | Exact match |
| `not` | Negation |
| `in` | Match any in array |
| `notIn` | Match none in array |

```ts
where: {
  role: "ADMIN",                    // Shorthand for equals
  role: { equals: "ADMIN" },        // Explicit equals
  role: { not: "GUEST" },           // Not equal
  role: { in: ["ADMIN", "MODERATOR"] },  // In array
  role: { notIn: ["GUEST", "BANNED"] },  // Not in array
}
```

A long same-column `OR` of equalities is sent as that column's `in`; other long
`OR`s are nested so SQLite's expression-depth limit is not reached.

:::info
An explicit `undefined` value is treated as if the key were absent, matching Prisma. `where: { role: role ?? undefined }` behaves the same as omitting `role` entirely — useful for building filters conditionally without stripping keys yourself.
:::

:::warning
A bulk write is the exception. `updateMany` and `deleteMany` (and the same
verbs nested in a relation write) refuse a `where` that matches every row only
because its `undefined` members were dropped:
`deleteMany({ where: { tenantId: undefined } })` throws a `ValidationError`
naming `tenantId` instead of deleting every row. `where: {}` or no `where` still
means every row. Reads keep the Prisma rule, so validate required filters before
using them elsewhere. An empty `AND` or `NOT` is true; an empty `OR`, or an `OR`
containing only empty objects after undefined keys are removed, is false. Build
optional alternatives before constructing `OR`.
:::

## Untrusted filter values

The object query API is a query language, not a values-only interface. Never
forward a request body directly into `where`, especially on authentication or
secret fields: `{ password: { not: "" } }` is a valid query operator. Validate
each external value as the primitive your application expects, allowlist
queryable fields, and construct the query on the server. Parameter binding
prevents SQL injection; it does not authorize query operators.

## Logical Operators

Combine conditions with `AND`, `OR`, `NOT`:

```ts
// AND (implicit)
where: {
  role: "ADMIN",
  active: true,
}

// AND (explicit)
where: {
  AND: [
    { role: "ADMIN" },
    { active: true },
  ],
}

// OR
where: {
  OR: [
    { role: "ADMIN" },
    { email: { endsWith: "@company.com" } },
  ],
}

// NOT
where: {
  NOT: { role: "GUEST" },
}

// Combined
where: {
  AND: [
    { active: true },
    {
      OR: [
        { role: "ADMIN" },
        { email: { endsWith: "@company.com" } },
      ],
    },
  ],
}
```

## Filter by Type

**[String Filters](/docs/client/filtering/string)**

contains, startsWith, endsWith, mode

**[Number Filters](/docs/client/filtering/number)**

lt, lte, gt, gte comparisons

**[Boolean Filters](/docs/client/filtering/boolean)**

equals, not operators

**[DateTime Filters](/docs/client/filtering/datetime)**

Date comparisons and ranges

**[JSON Filters](/docs/client/filtering/json)**

Path queries and operations

**[Array Filters](/docs/client/filtering/arrays)**

has, hasEvery, hasSome, isEmpty

**[GeoPoint Filters](/docs/schema/scalars/point)**

Exact coordinates, bounds, polygons, and distance

**[Field references and SQL fragments](/docs/client/filtering/field-references)**

Compare a column against another column of the same row, or against SQL you write
