JSONB filters and mutations

JSONB columns are where type safety usually goes to die. Settings, feature flags, billing metadata, event payloads - the data is structured, but every query against it turns into a hand-written sql string with operators, path arrays, and casts that TypeScript cannot check.

better-drizzle closes that gap. Declare the shape once with Drizzle's $type<T>(), then filter scalar leaves with concise dot paths and the same operators you already use for regular columns:

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

What you get:

• Concise path syntax. 'plan.tier', 'plan.seats', and 'notifications.email.enabled' map directly to JSONB paths.
• Scalar filter operators. Numbers support gte/lt, strings support startsWith/contains, and booleans support equality.
• Type-guarded SQL. Every predicate checks jsonb_typeof(...) before comparing, 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.
• It composes. JSON paths work next to regular columns, inside AND / OR / NOT, inside relation filters, and in every helper that takes where.

Define the JSON shape

Add .$type<T>() to document the shape and enable exact path and leaf validation for filters and mutations:

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

The declared shape contains these scalar paths:

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.

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

Operators by leaf type

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

Legacy JSON filter wrapper

The former { json: { ... } } filter wrapper remains supported but is deprecated. Use it for a root path without a dot, for an object whose literal key contains a dot, or when its stricter path and leaf validation is useful during migration; dotted shorthand would otherwise be ambiguous with JSON document equality. Mutation path typing is described below; both mutation forms validate typed paths and values.

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

String matching is case-sensitive by default. Add mode: 'insensitive' to switch contains, startsWith, and endsWith to ILIKE.

Examples

Equality and string unions

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

const enterprise = await client.accounts.findMany({
	where: {
		settings: { 'plan.tier': 'enterprise' },
	},
});

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

Numeric ranges

Combine comparison operators on the same path to express a range:

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

Deeply nested flags

Paths can go as deep as the type does:

const dailyDigest = await client.accounts.findMany({
	where: {
		settings: {
			'notifications.email.enabled': true,
			'notifications.email.digest': 'daily',
		},
	},
});

String matching

const acmeReferrals = await client.accounts.findMany({
	where: {
		settings: { json: { referrer: { endsWith: '@acme.com' } } },
	},
});

Lists and case-insensitive matching

in and notIn take a list of allowed or excluded values, and mode: 'insensitive' makes pattern matching ignore case:

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

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

Negation

not accepts a value or another filter object for the same leaf:

const paying = await client.accounts.findMany({
	where: {
		settings: {
			'plan.tier': { not: 'free' },
			'plan.seats': { not: { lt: 5 } },
		},
	},
});

JSON null values

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

See missing keys below for how this differs from a key that is not present at all.

OR, AND, and NOT across paths

JSON filters are ordinary where entries, so logical operators work as usual:

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

Mixing JSON paths with regular columns

const proNamedA = await client.accounts.findMany({
	where: {
		name: { startsWith: 'A' },
		settings: { 'plan.tier': 'pro' },
	},
	select: { id: true, name: true, settings: true },
});

Inside relation filters

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

Every helper that takes where

The same filter object works for counting, existence checks, pagination, and bulk writes - no separate API to learn:

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

const total = await client.accounts.count({ where: onTrial });
const anyOnTrial = await client.accounts.exists({ where: onTrial });

const page = await client.accounts.paginate({
	where: onTrial,
	orderBy: [{ id: 'asc' }],
	limit: 20,
});

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

await client.orders.deleteMany({
	where: { payload: { json: { currency: 'EUR', total: { lt: 1 } } } },
});

Semantics worth knowing

Values of the wrong JSON type do not match

Each predicate is guarded by jsonb_typeof. If one row stores "seats": "42" (a string) instead of 42, a numeric filter such as 'plan.seats': { gte: 10 } skips that row instead of comparing a string against a number. The same applies to strings and booleans.

Missing keys never match

A path that does not exist in the document evaluates to SQL NULL, so no JSON filter matches it - including not and notIn:

// Only rows whose document has a `referrer` key that is not 'partner'.
// Rows without `referrer` at all are excluded.
await client.accounts.findMany({
	where: { settings: { json: { referrer: { not: 'partner' } } } },
});

To include rows where the key is missing, add an explicit branch with a Drizzle sql fragment:

import { sql } from 'drizzle-orm';

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

Likewise, nickname: null matches only documents that contain "nickname": null, not documents where nickname is absent.

What SQL it generates

A filter such as 'plan.seats': { gte: 18 } compiles to:

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

Use .explain() on any read to see the exact statement and plan for your query.

Indexing JSON paths

Filters on a JSON path can use a PostgreSQL expression index on the same extraction and cast. For a hot numeric path:

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

For string and boolean paths, index the text extraction:

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

Then confirm the planner picks it up:

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 operators such as @>, but does not help these path comparisons. Index the specific paths you filter on.

JSONB path mutations

The structured mutation operator is set a path to a JSON value. Pass dotted object paths to update part of a document, or replace the whole document by passing a regular JSON value. There is no set envelope: the value assigned to each path is the value written there. For a column declared with $type<T>(), both the dotted shorthand and { json: ... } wrapper check each path and value against T at compile time.

await client.accounts.update({
	where: { id },
	data: { settings: { 'plan.tier': 'pro', 'plan.seats': 25 } },
});

Where path mutations work

upsertMany with update: 'all' or update: ['settings'] copies the proposed full document; use an object or callback for path mutation. create and createMany insert complete JSON documents.

Path behavior and limits

• Every shorthand key must contain a dot. Use { json: { nickname: 'Ada' } } for a top-level key. The wrapper must be the only key and contain at least one path. Untyped JSONB columns accept open path names and JSON-encodable values.
• Paths address object keys, not array indexes. JSON objects and arrays are valid values at a path. A dot always separates path segments, including inside the { json: ... } wrapper; literal keys containing a dot have no structured mutation syntax. A top-level json key holding a plain object is reserved for the wrapper.
• Missing object ancestors are created. SQL NULL and non-object JSONB roots become {}; non-object intermediate values are replaced by {}. Existing object keys outside the assigned paths are preserved.
• Mixing dotted and plain keys, overlapping ancestor and descendant paths, empty wrappers, nested undefined, and values that cannot be encoded as JSON throw OPERATION_ERROR. PostgreSQL alone supports path mutations; other dialects throw JSONB_MUTATION_UNSUPPORTED. Full-document replacement still works wherever the underlying column supports it.

There are no structured path operators for deleting a key, merging an object, incrementing a number, or modifying array elements. null writes JSON null rather than deleting a key. For those operations, pass a Drizzle sql expression as the entire column update value.

Limits and dialect support

• PostgreSQL only for path shapes. SQLite and MySQL reject json filters with JSONB_QUERY_UNSUPPORTED (and dotted/wrapper path mutations with JSONB_MUTATION_UNSUPPORTED) instead of silently changing semantics. Full-document replacements are not gated.
• Scalar object paths only for filtering. Arrays, object containment (@>), PostgreSQL JSONPath strings (@?, @@), and keys that contain a dot are intentionally outside the structured filter API. Mutations address object-key paths and accept JSON objects and arrays as values.
• Filtering and partial updates. JSON paths are available in where and in update data; ordering by a JSON path is not part of the structured API.

For anything outside those limits, pass a Drizzle sql fragment as the where:

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