Combining JSONB filters

Use these patterns once a single JSONB filter works and the query needs more: other columns, alternatives, related tables, or an index. The examples use the accounts.settings shape from that page.

Combine paths, columns, and logic

JSON filters are ordinary where entries, so they sit next to regular columns and inside OR, AND, and NOT:

const upsellTargets = await client.accounts.findMany({
	where: {
		name: { startsWith: 'A' },
		OR: [
			{ settings: { 'plan.trial': true } },
			{ settings: { 'plan.tier': 'free', 'plan.seats': { gte: 3 } } },
		],
		NOT: { settings: { 'notifications.email.digest': 'never' } },
	},
	select: { id: true, name: true, settings: true },
});

Filter through relations

JSON paths work on related tables through some, every, none, and is. Given an orders table with a typed payload column:

// accounts with at least one USD order above 100
const bigSpenders = await client.accounts.findMany({
	where: {
		orders: {
			some: { payload: { json: { currency: 'USD', total: { gt: 100 } } } },
		},
	},
});

// orders placed by enterprise accounts
const enterpriseOrders = await client.orders.findMany({
	where: {
		account: { is: { settings: { 'plan.tier': 'enterprise' } } },
	},
	include: { account: true },
});

The same filter object works in count, exists, paginate, cursor, updateMany, and deleteMany:

const onTrial = { settings: { 'plan.trial': true } } as const;

const total = await client.accounts.count({ where: onTrial });
const { count: expired } = await client.accounts.updateMany({
	where: { ...onTrial, name: { startsWith: 'legacy-' } },
	data: { name: 'expired' },
});

Index the paths you filter on

A path filter can use a PostgreSQL expression index on the same extraction and cast. Numeric paths index the cast, string and boolean paths index the text extraction:

create index accounts_plan_seats_idx
	on accounts (((settings #>> '{plan,seats}')::numeric));

create index accounts_plan_tier_idx
	on accounts ((settings #>> '{plan,tier}'));

Confirm the planner uses it with .explain():

const plan = await client.accounts
	.findMany({ where: { settings: { 'plan.seats': { gte: 998 } } } })
	.explain({ analyze: true });

// Bitmap Index Scan on accounts_plan_seats_idx
//   Index Cond: (((settings #>> '{plan,seats}'::text[]))::numeric >= '998'::numeric)

A GIN index on the whole column (using gin (settings)) speeds up containment (@>) but not these path comparisons.