# Filter syntax

> The full grammar of the language that API rules and list filters share, PocketBase's own, compiled to SQL.

API rules and the `filter` of a list use one language: PocketBase's. The compiler turns it into one SQL expression with every value bound as a parameter. How rules apply per action: [Rules and filters](https://golocomotive.fr/docs/developers/rules.md).

## Operands

| operand | example |
|---|---|
| a field path | `status`, `client.name`, `meta.address.city` |
| text, single or double quoted | `'open'`, `"done"`, `'it\'s'`; escapes `\n \t \r \\ \' \"` |
| a number | `12`, `-1.5` (no exponent) |
| a literal | `true`, `false`, `null` |
| a request value | `@request.auth.id` |
| a macro | `@now`, `@todayStart` |
| a function | `geoDistance(lon, lat, 2.35, 48.85)` |

`//` starts a comment to the end of the line. Whitespace is space, tab and newline.

## Operators

| operator | meaning | any of |
|---|---|---|
| `=` | equal | `?=` |
| `!=` | not equal | `?!=` |
| `>` `>=` `<` `<=` | compare | `?>` `?>=` `?<` `?<=` |
| `~` | contains (`LIKE`) | `?~` |
| `!~` | does not contain | `?!~` |

- Without `?`, a multiple value (multi select, multi relation, back relation) must match for ALL its values. With `?`, for at least one.
- `=` and `!=` treat `''` and NULL as equal: `x = ''` matches a NULL. `!=` is `IS NOT`.
- `~` wraps a literal in `%…%` unless it already holds an unescaped `%`. `_`, `%` and `\` in it are escaped. Against a field, it is `LIKE ('%' || field || '%')`. `LIKE` folds ASCII case only, never accents.
- Numbers compare as REAL, like PocketBase: `title = 5` does not match the text `'5'`.

## Joins

`&&` (and), `||` (or), parentheses. AND binds before OR, as in SQL. A group must stand where an operand starts: `a = (b = 1)` is refused.

## Paths

| path | reaches |
|---|---|
| `field` | a column of the collection |
| `rel.field` | through a relation, single or multiple, nested up to 6 levels |
| `rel.id` | the same as `rel` |
| `json.a.0.b` | inside a JSON field |
| `geo.lon`, `geo.lat` | a geo point |
| `posts_via_author.title` | a back relation: the `posts` whose `author` points here. Multiple, unless `author` has a single column unique index |

A bare multiple column (`tags` with no path or modifier) compares its raw JSON text. Write `tags:each ?= "x"`, `editors.id ?= "x"` or `tags ~ "x"` instead.

## Request values

| path | value |
|---|---|
| `@request.auth.<field>` | the signed in record: `id collectionId collectionName email emailVisibility verified` read from it, any other field joined, relations included |
| `@request.body.<field>` | the submitted data. `@request.body.<rel>.<field>` looks up the records the body names |
| `@request.query.<key>` | the query string |
| `@request.headers.<key>` | a header, key snake cased: `X-Token` is `x_token` |
| `@request.method` | the HTTP method |
| `@request.context` | `default`, `expand`, `realtime`, `protectedFile`, `batch`, `oauth2`, `otp`, `password`, or `mcp` |
| `@collection.<name>[:alias].<field>` | another collection, cross joined. All of, unless `?` |

A key the request does not carry is NULL. A client filter that is not a superuser's cannot use `@request.` or `@collection.`.

## Modifiers

| modifier | on | meaning |
|---|---|---|
| `:isset` | `@request.*` | the key was sent |
| `:changed` | `@request.body.<field>` | sent, and different from the stored value |
| `:length` | select, file, relation, also in the body | the number of values |
| `:each` | select, file, relation, also in the body | each value, so `?=` and `=` apply per value |
| `:lower` | any field or request value | lowercased |

## Macros

All in UTC.

| macro | value |
|---|---|
| `@now`, `@yesterday`, `@tomorrow` | a datetime, `2006-01-02 15:04:05.000Z` form |
| `@todayStart`, `@todayEnd`, `@monthStart`, `@monthEnd`, `@yearStart`, `@yearEnd` | a datetime |
| `@second`, `@minute`, `@hour`, `@day`, `@month`, `@weekday`, `@year` | an integer |

## Functions

- `geoDistance(lonA, latA, lonB, latB)`: the distance in kilometers.
- `strftime(format, [time, modifiers…])`: SQLite's date formatting.

## Parameters

`{:name}` in a filter takes the value of `name` from its parameters, quoted and escaped. Use `bindFilter` in the client and `filter` on the server:

```ts
bindFilter('status = {:s} && created >= {:since}', { s: 'open', since: new Date('2026-01-01') })
// status = "open" && created >= "2026-01-01 00:00:00.000Z"
```

## Sort

A comma list of fields and relation paths, `-` for descending, `+` or nothing for ascending, `@random`, `@rowid`. At most 8 fields.

## Limits

| limit | value |
|---|---|
| expressions in one filter | 200 |
| characters in a client filter | 3500 |
| fields in a sort | 8 |