---
title: JSON Filters
description: Filter operators for JSON scalars
icon: braces
---

## Operators

| Operator | Description |
|----------|-------------|
| `equals` | Match the JSON value exactly (or a [null sentinel](#the-two-nulls)) |
| `not` | Negate a nested JSON filter (or a [null sentinel](#the-two-nulls)) |
| `path` | Scope the other operators to a nested value |
| `mode` | `"default"` (exact) or `"insensitive"` — scopes the `string_*` operators |
| `lt` / `lte` / `gt` / `gte` | Compare the value at the path — see [Comparisons](#comparisons) |
| `string_contains` | String at path contains substring |
| `string_starts_with` | String at path starts with |
| `string_ends_with` | String at path ends with |
| `array_contains` | Array at path contains all given value(s) |
| `array_starts_with` | First array element equals value |
| `array_ends_with` | Last array element equals value |

`path` and `mode` are modifiers rather than operations: they scope the other
operators in the same filter object, and a nested `not` inherits both unless it
sets its own.

## Path Queries

`path` scopes every other operator in the same filter object to the nested
value. It takes two spellings — the portable array of segments, and Prisma's
`"$.a.b"` string form. Integer segments (`"0"`, `"1"`, …) address array
elements:

```ts
// Exact match at path
where: {
  metadata: {
    path: ["settings", "theme"],
    equals: "dark",
  },
}

// Array element by index
where: {
  metadata: {
    path: ["pet", "toys", "0"],
    equals: "ball",
  },
}

// The same two queries in the string form
where: { metadata: { path: "$.settings.theme", equals: "dark" } }
where: { metadata: { path: "$.pet.toys[0]", equals: "ball" } }
```

The two forms mean the same thing and every operator accepts either. `"$"`
alone is the document root.

The string grammar is exactly `"$"`, `"$.key"` and `"$.key[0]"` — anything else
(a bare `"theme"`, a trailing `"$."`, `"$.a[*]"`, a negative index) is rejected
before SQL generation rather than guessed at. **A dot is always a separator**,
so a key that itself contains `.`, `[` or `]` is only addressable through the
array form:

```ts
// The document is { "weird.key": "gotcha" }
where: { metadata: { path: "$.weird.key", equals: "gotcha" } }   // matches nothing
where: { metadata: { path: ["weird.key"], equals: "gotcha" } }   // matches
```

For one portable grammar, path segments containing `"` or `\` are rejected
before SQL generation on every database — in either spelling.

Rows where the path does not exist never match. Filtering `equals: null`
with a `path` matches an explicit JSON `null` at that path (not missing
keys); without a `path` it matches rows where the column is SQL `NULL`.

## Comparisons

`lt`, `lte`, `gt` and `gte` compare the value at the path (or the document root
when there is no `path`). The semantics are identical on PostgreSQL, MySQL and
SQLite:

```ts
// Numbers
where: { metadata: { path: ["score"], gt: 10 } }

// Strings
where: { metadata: { path: ["tier"], gte: "gold" } }

// Several comparisons in one object are ANDed
where: { metadata: { path: ["score"], gt: 1, lt: 10 } }
```

**The operand's JavaScript type picks the comparison class.** A number compares
numerically and only against JSON numbers; a string compares lexicographically
by code point and only against JSON strings. The classes never cross: the JSON
string `"42"` does not satisfy `gt: 40`, and the JSON number `42` does not
satisfy `gt: "40"`. Any other operand type is rejected.

Ordering of strings is code-point ordering, not the database's locale
collation, so `"Banana"` sorts before `"apple"` on every provider.

A row whose path is absent, whose column is `NULL`, or whose value at the path
is of the other class (or a boolean, JSON `null`, object or array) never matches
and never errors.

## The two nulls

A nullable JSON column can be null in two different ways, and SQL can tell
them apart even though JavaScript cannot:

- the **database NULL** — the column holds no document at all;
- the **JSON null** — the column holds a document, and that document is the
  JSON value `null`.

Three exported sentinels name them (the same three Prisma exports):

```ts
import { AnyNull, DbNull, JsonNull } from "viborm";

await client.entry.findMany({ where: { meta: { equals: DbNull } } });
await client.entry.findMany({ where: { meta: { equals: JsonNull } } });
await client.entry.findMany({ where: { meta: { equals: AnyNull } } });
```

The truth table is identical on PostgreSQL, MySQL and SQLite:

| Filter | Matches |
|--------|---------|
| `equals: DbNull` | rows whose column is SQL `NULL` |
| `equals: JsonNull` | rows whose column holds the JSON value `null` |
| `equals: AnyNull` | either of the above |
| `not: DbNull` | every row that holds a document, JSON nulls included |
| `not: JsonNull` | rows holding a document other than `null` (SQL `NULL` rows are excluded, as for any value comparison) |
| `not: AnyNull` | rows holding a document other than `null` |

Both nulls read back as JavaScript `null`, so the sentinels are how you tell
which one a row holds.

A sentinel describes the whole column, so it cannot be combined with `path`;
that combination is rejected before SQL is generated. To test for a JSON
`null` at a path, use `path` with `equals: null`.

`AnyNull` is filter-only — see [writing null](/docs/schema/scalars/json#writing-null)
for the write side.

## Negation

`not` takes a nested JSON filter and inherits the outer `path` unless the
nested filter sets its own:

```ts
// theme present and not "dark"
where: {
  metadata: {
    path: ["theme"],
    not: { equals: "dark" },
  },
}
```

Rows where the path is missing (or the column is `NULL`) are excluded,
following SQL `NOT` semantics.

## String Operations

String operators match against the string at the path (or the document
root for string-valued columns). The value is matched literally — `%` and
`_` have no wildcard meaning:

```ts
where: {
  metadata: {
    path: ["theme"],
    string_contains: "dark",
  },
}
```

### Case-insensitive matching

Matching is case-sensitive by default. `mode: "insensitive"` folds the
comparison, exactly as it does on [string scalars](/docs/client/filtering/string):

```ts
where: {
  metadata: {
    path: ["theme"],
    string_contains: "DARK",
    mode: "insensitive",
  },
}
```

- `mode` governs `string_contains`, `string_starts_with` and
  `string_ends_with` only. `equals`, `not` and the `array_*` operators compare
  whole JSON values rather than text, so folding them would mean nothing.
- Case is folded the way the database does natively, exactly as for string
  scalars, so JSON and scalar filters agree with each other. `"ÉCL"` matches
  `"Éclair"` everywhere; `"écl"` matches it where the database folds `É`
  (PostgreSQL with a libc or ICU locale, MySQL) and not on SQLite. See
  [String filters](/docs/client/filtering/string).
- Wildcards stay literal under the fold: `%` and `_` have no special meaning.
- A nested `not` inherits the mode, and may override it in either direction —
  `not: { string_contains: "x", mode: "default" }` really does restore exact
  matching on that arm.
- A `mode: "insensitive"` with no string operator to govern is **refused**, not
  silently ignored.

## Array Operations

```ts
// Array at path contains a value
where: {
  metadata: {
    path: ["tags"],
    array_contains: "featured",
  },
}

// Contains ALL of the listed values (any order)
where: {
  metadata: {
    path: ["roles"],
    array_contains: ["admin", "moderator"],
  },
}

// First / last element
where: {
  metadata: { path: ["tags"], array_starts_with: "admin" },
}
where: {
  metadata: { path: ["tags"], array_ends_with: "beta" },
}
```

`array_contains` only matches when the value at the path is an array.

JSON equality ignores object property order and preserves array order and value
types. SQLite compares array members structurally, including nested objects;
PostgreSQL and MySQL additionally support their native partial-object containment
semantics. Use complete member values when the same filter must run on all three.
