# JSONB filters

> Filter PostgreSQL JSONB columns with typed dot paths and the same operators as regular columns.

URL: https://better-drizzle.com/docs/querying/jsonb

Applies to: postgresql

(Added in 0.3.0)

Use JSONB filters when you query values stored inside a PostgreSQL `jsonb` column (settings, feature flags, event payloads) and want typed paths instead of hand-written `sql` strings.



**better-drizzle**

```ts  
const accounts = await client.accounts.findMany({
	where: {
		settings: {
			'plan.tier': 'pro',
			'plan.seats': { gte: 10 },
			'notifications.email.enabled': true,
		},
	},
});
```

**Drizzle**

```ts  
import { and, sql } from 'drizzle-orm';

const rows = await db
	.select()
	.from(accounts)
	.where(
		and(
			sql`jsonb_typeof(${accounts.settings} #> '{plan,tier}') = 'string'`,
			sql`${accounts.settings} #>> '{plan,tier}' = ${'pro'}`,
			sql`jsonb_typeof(${accounts.settings} #> '{plan,seats}') = 'number'`,
			sql`(${accounts.settings} #>> '{plan,seats}')::numeric >= ${10}`,
			sql`jsonb_typeof(${accounts.settings} #> '{notifications,email,enabled}') = 'boolean'`,
			sql`(${accounts.settings} #>> '{notifications,email,enabled}')::boolean = ${true}`,
		),
	);
```

* **Dot paths.** `'plan.tier'` and `'notifications.email.enabled'` map to JSONB paths.
* **Scalar operators.** Numbers take `gte`/`lt`, strings take `startsWith`/`contains`, booleans take equality.
* **Type-guarded SQL.** Every predicate checks `jsonb_typeof(...)` first, so a row that stores `"42"` (a string) never matches a numeric filter.
* **Bound parameters.** Path segments and values are sent as parameters, never interpolated.
* **Composable.** Paths work next to regular columns, inside `AND` / `OR` / `NOT`, inside relation filters, and in every helper that takes `where`.

To combine paths with other filters and relations, or to index them, see [combining JSONB filters](/docs/querying/jsonb-combining). To update paths, see [JSONB updates](/docs/querying/jsonb-updates).

## Define the JSON shape

Add `.$type<T>()` to the column. It enables exact path and leaf validation for filters and updates:

```ts title="schema.ts"
import { integer, jsonb, pgTable, text } from 'drizzle-orm/pg-core';

export type AccountSettings = {
	plan: {
		tier: 'free' | 'pro' | 'enterprise';
		seats: number;
		trial: boolean;
	};
	notifications: {
		email: { enabled: boolean; digest: 'daily' | 'weekly' | 'never' };
	};
	nickname: string | null;
	referrer?: string;
	tags: string[];
};

export const accounts = pgTable('accounts', {
	id: integer('id').primaryKey(),
	name: text('name').notNull(),
	settings: jsonb('settings').$type<AccountSettings>().notNull(),
});
```

Nested objects are flattened into dot paths, optional keys and nullable leaves are kept, and arrays are left out:

```ts
type SettingsPath =
	| 'plan.tier' // 'free' | 'pro' | 'enterprise'
	| 'plan.seats' // number
	| 'plan.trial' // boolean
	| 'notifications.email.enabled' // boolean
	| 'notifications.email.digest' // 'daily' | 'weekly' | 'never'
	| 'nickname' // string | null
	| 'referrer'; // string (optional)
// `tags` is an array, so it is not addressable as a path.
```

A plain `jsonb('metadata')` column without `$type` has the type `unknown`. It accepts dotted path filters with open path names and JSON scalar values, but not the `{ json: ... }` wrapper.

## Filter root-level keys with `json`

Dotted keys directly on the column are the shorthand for nested paths. A key without a dot would be read as JSON document equality, so root keys such as `nickname` go inside `{ json: { ... } }`. The wrapper also accepts dotted paths:

```ts
await client.accounts.findMany({
	where: { settings: { json: { nickname: { contains: 'Ada' } } } },
});
```

## Match values and unions

A plain value is shorthand for `equals`. String-union leaves accept only their literal members:



**better-drizzle**

```ts  
const enterprise = await client.accounts.findMany({
	where: {
		settings: {
			'plan.tier': 'enterprise',
			'notifications.email.digest': 'daily',
		},
	},
});

// @ts-expect-error 'gold' is not a valid tier
await client.accounts.findMany({ where: { settings: { json: { 'plan.tier': 'gold' } } } });
```

**Drizzle**

```ts  
import { and, sql } from 'drizzle-orm';

const enterprise = await db
	.select()
	.from(accounts)
	.where(
		and(
			sql`jsonb_typeof(${accounts.settings} #> '{plan,tier}') = 'string'`,
			sql`${accounts.settings} #>> '{plan,tier}' = ${'enterprise'}`,
			sql`jsonb_typeof(${accounts.settings} #> '{notifications,email,digest}') = 'string'`,
			sql`${accounts.settings} #>> '{notifications,email,digest}' = ${'daily'}`,
		),
	);

// no compile-time check: a typo in the path or value silently matches nothing
```

## Ranges and negation

Several operators on one path are combined with `AND`. `not` takes a value or another filter object for the same leaf:



**better-drizzle**

```ts  
const midMarket = await client.accounts.findMany({
	where: {
		settings: {
			'plan.seats': { gte: 10, lt: 500 },
			'plan.tier': { not: 'free' },
		},
	},
	orderBy: { id: 'asc' },
});
```

**Drizzle**

```ts  
import { and, asc, sql } from 'drizzle-orm';

const seats = sql`(${accounts.settings} #>> '{plan,seats}')::numeric`;
const tier = sql`${accounts.settings} #>> '{plan,tier}'`;

const midMarket = await db
	.select()
	.from(accounts)
	.where(
		and(
			sql`jsonb_typeof(${accounts.settings} #> '{plan,seats}') = 'number'`,
			sql`${seats} >= ${10}`,
			sql`${seats} < ${500}`,
			sql`not (jsonb_typeof(${accounts.settings} #> '{plan,tier}') = 'string' and ${tier} = ${'free'})`,
		),
	)
	.orderBy(asc(accounts.id));
```

## Lists, patterns, and case

`in` and `notIn` take a list. String matching is case-sensitive by default; `mode: 'insensitive'` switches `contains`, `startsWith`, and `endsWith` to `ILIKE` and compares `equals`, `in`, and `notIn` as `lower(value)`:



**better-drizzle**

```ts  
const paidTiers = await client.accounts.findMany({
	where: {
		settings: {
			json: {
				'plan.tier': { in: ['pro', 'enterprise'] },
				'notifications.email.digest': { notIn: ['never'] },
				nickname: { startsWith: 'ana', mode: 'insensitive' },
				referrer: { endsWith: '@acme.com' },
			},
		},
	},
});
```

**Drizzle**

```ts  
import { and, sql } from 'drizzle-orm';

const paidTiers = await db
	.select()
	.from(accounts)
	.where(
		and(
			sql`jsonb_typeof(${accounts.settings} #> '{plan,tier}') = 'string'`,
			sql`${accounts.settings} #>> '{plan,tier}' in (${'pro'}, ${'enterprise'})`,
			sql`not (jsonb_typeof(${accounts.settings} #> '{notifications,email,digest}') = 'string'
				and ${accounts.settings} #>> '{notifications,email,digest}' in (${'never'}))`,
			sql`jsonb_typeof(${accounts.settings} #> '{nickname}') = 'string'`,
			sql`${accounts.settings} #>> '{nickname}' ilike ${'ana%'}`,
			sql`jsonb_typeof(${accounts.settings} #> '{referrer}') = 'string'`,
			sql`${accounts.settings} #>> '{referrer}' like ${'%@acme.com'}`,
		),
	);
```

Each list compiles to one type-guarded `IN (...)`, not one `OR` branch per value. An empty `in: []` matches nothing.

## JSON `null`

For nullable leaves, `null` matches an explicit JSON `null` and `not: null` matches any non-null value:



**better-drizzle**

```ts  
// { "nickname": null }
const withoutNickname = await client.accounts.findMany({
	where: { settings: { json: { nickname: null } } },
});

// { "nickname": "..." }
const withNickname = await client.accounts.findMany({
	where: { settings: { json: { nickname: { not: null } } } },
});
```

**Drizzle**

```ts  
import { not, sql } from 'drizzle-orm';

const isNull = sql`jsonb_typeof(${accounts.settings} #> '{nickname}') = 'null'`;

const withoutNickname = await db.select().from(accounts).where(isNull);
const withNickname = await db.select().from(accounts).where(not(isNull));
```

A document without the `nickname` key matches neither filter; see [missing keys](#missing-keys-never-match).

## Edge cases

**Values of the wrong JSON type do not match**

Each predicate is guarded by `jsonb_typeof`. If a row stores `"seats": "42"` (a string), `'plan.seats': { gte: 10 }` skips it instead of comparing a string with a number. The same applies to strings and booleans.

**Missing keys never match**

A path missing from the document evaluates to SQL `NULL`, so no JSON filter matches it, &#x2A;*including `not` and `notIn`**. `nickname: null` matches only documents that contain `"nickname": null`.

```ts
// Rows without a `referrer` key are excluded.
await client.accounts.findMany({
	where: { settings: { json: { referrer: { not: 'partner' } } } },
});
```

To include rows where the key is missing, pass a Drizzle `sql` fragment:

```ts
import { sql } from 'drizzle-orm';

await client.accounts.findMany({
	where: sql`not (${accounts.settings} ? 'referrer') or ${accounts.settings} ->> 'referrer' <> 'partner'`,
});
```

**What SQL it generates**

`'plan.seats': { gte: 18 }` compiles to:

```sql
where jsonb_typeof("settings" #> ARRAY[$1, $2]::text[]) = $3
  and ("settings" #>> ARRAY[$4, $5]::text[])::numeric >= $6
-- params: ['plan', 'seats', 'number', 'plan', 'seats', 18]
```

**Arrays, containment, and JSONPath**

Arrays, object containment (`@>`), PostgreSQL JSONPath (`@?`, `@@`), keys that contain a dot, and ordering by a JSON path are outside the structured API. Pass a Drizzle `sql` fragment as the `where`:

```ts
import { sql } from 'drizzle-orm';

// array membership: accounts tagged 'beta'
const beta = await client.accounts.findMany({
	where: sql`${accounts.settings} -> 'tags' ? 'beta'`,
});

// containment, using a GIN index on settings
const proTrials = await client.accounts.findMany({
	where: sql`${accounts.settings} @> ${JSON.stringify({ plan: { tier: 'pro', trial: true } })}::jsonb`,
});
```

## Reference

| Leaf type                      | Shorthand         | Operators                                                                    |
| ------------------------------ | ----------------- | ---------------------------------------------------------------------------- |
| `string` (incl. string unions) | `'path': 'value'` | `equals`, `in`, `notIn`, `contains`, `startsWith`, `endsWith`, `mode`, `not` |
| `number` / `bigint`            | `'path': 42`      | `equals`, `in`, `notIn`, `lt`, `lte`, `gt`, `gte`, `not`                     |
| `boolean`                      | `'path': true`    | `equals`, `not`                                                              |
| nullable leaf (`T \| null`)    | `'path': null`    | everything above, plus `null` / `not: null`                                  |

Several paths inside one JSONB filter, and several operators on one path, are combined with `AND`.

| Limit       | Behavior                                                                                                                                           |
| ----------- | -------------------------------------------------------------------------------------------------------------------------------------------------- |
| Dialect     | PostgreSQL only. SQLite and MySQL reject `json` filters with `JSONB_QUERY_UNSUPPORTED` instead of changing semantics.                              |
| Column type | `jsonb` only. On a `json` column, path filters throw `JSON path filters require a jsonb column; json columns only support whole-document filters.` |
| Paths       | Scalar object paths only; a dot always separates segments.                                                                                         |
| Ordering    | Not part of the structured API.                                                                                                                    |