JSONB filters

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.

const accounts = await client.accounts.findMany({
	where: {
		settings: {
			'plan.tier': 'pro',
			'plan.seats': { gte: 10 },
			'notifications.email.enabled': 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. To update paths, see JSONB updates.

Define the JSON shape

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

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:

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:

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:

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' } } } });

Ranges and negation

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

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

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):

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' },
			},
		},
	},
});

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:

// { "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 } } } },
});

A document without the nickname key matches neither filter; see missing keys.

Edge cases

Reference

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