# Array filters > Typed filters for PostgreSQL array columns - membership, containment, length, and element predicates. URL: https://better-drizzle.com/docs/querying/arrays Applies to: postgresql (Added in 0.3.0) Use array filters when a PostgreSQL array column (tags, roles, permissions) decides which rows you read, and you want typed operators instead of `@>`, `&&`, `<@`, and `cardinality()` in raw SQL. **better-drizzle** ```ts const rows = await client.posts.findMany({ where: { tags: { has: 'drizzle', length: { lte: 5 } }, editors: { hasSome: [currentUserId] }, flags: { hasNone: ['archived', 'spam'] }, }, }); ``` **Drizzle** ```ts import { and, arrayContains, arrayOverlaps, not, sql } from 'drizzle-orm'; const rows = await db .select() .from(posts) .where( and( arrayContains(posts.tags, ['drizzle']), sql`cardinality(${posts.tags}) <= ${5}`, arrayOverlaps(posts.editors, [currentUserId]), not(arrayOverlaps(posts.flags, ['archived', 'spam'])), ), ); ``` * **Operators named after the question.** `has`, `hasEvery`, `hasSome`, `hasNone`, `containedBy`, `isEmpty`, and `length` instead of `@>`, `&&`, `<@`, and `cardinality()`. * **Element types from your schema.** A `text[]` column takes strings, an `integer[]` takes numbers, and an enum array only accepts its enum values, with autocomplete. * **Correct parameter binding.** Values are bound as arrays of the column's own type, so enum and `uuid` arrays work without manual casts. * **Index-friendly SQL.** Containment and overlap compile to `@>`, `&&`, and `<@`, which a GIN index serves directly. * **Everywhere `where` works.** `findMany`, `count`, `exists`, `paginate`, `cursor`, `updateMany`, `deleteMany`, and relation filters. * **Fail fast outside PostgreSQL.** An array filter on another dialect throws instead of silently matching nothing. To change array contents, see [array updates](/docs/querying/array-updates). To index array columns, see [array indexing](/docs/querying/array-indexing). ## Define array columns Use Drizzle's `.array()` on any PostgreSQL column. Nothing else is needed: better-drizzle detects native array columns and switches their `where` type to the array filter. ```ts import { integer, pgEnum, pgTable, text, uuid } from 'drizzle-orm/pg-core'; export const role = pgEnum('role', ['admin', 'editor', 'viewer']); export const posts = pgTable('posts', { id: integer('id').primaryKey(), title: text('title').notNull(), tags: text('tags').array().notNull(), editors: uuid('editors').array().notNull(), roles: role('roles').array().notNull(), scores: integer('scores').array(), flags: text('flags').array(), }); ``` Only native array columns get these operators. A `jsonb` column typed as an array (`jsonb().$type()`) is JSON, not a PostgreSQL array, so it keeps the regular [JSONB filters](/docs/querying/jsonb). ## Match elements A plain array (or `equals`) is exact equality: same elements, same order, same duplicates. `has` asks whether one element is present, `hasEvery` / `hasSome` / `hasNone` take a list, and `containedBy` requires every element of the row to come from your list (a natural fit for permission checks). Enum arrays only accept their enum values: **better-drizzle** ```ts await client.posts.findMany({ where: { tags: ['drizzle', 'orm'] } }); // 'admin' | 'editor' | 'viewer' const admins = await client.users.findMany({ where: { roles: { has: 'admin' } } }); await client.posts.findMany({ where: { tags: { hasEvery: ['drizzle', 'postgres'] } } }); // both await client.posts.findMany({ where: { tags: { hasSome: ['drizzle', 'prisma'] } } }); // either await client.posts.findMany({ where: { flags: { hasNone: ['archived', 'spam'] } } }); // neither // only roles the current user can manage await client.posts.findMany({ where: { roles: { containedBy: ['editor', 'viewer'] } } }); ``` **Drizzle** ```ts import { arrayContained, arrayContains, arrayOverlaps, eq, not } from 'drizzle-orm'; await db.select().from(posts).where(eq(posts.tags, ['drizzle', 'orm'])); const admins = await db.select().from(users).where(arrayContains(users.roles, ['admin'])); await db.select().from(posts).where(arrayContains(posts.tags, ['drizzle', 'postgres'])); await db.select().from(posts).where(arrayOverlaps(posts.tags, ['drizzle', 'prisma'])); await db.select().from(posts).where(not(arrayOverlaps(posts.flags, ['archived', 'spam']))); await db.select().from(posts).where(arrayContained(posts.roles, ['editor', 'viewer'])); ``` Values are bound as an array of the column's own type, so enum and `uuid` arrays need no casts: `editors: { has: '00000000-0000-0000-0000-000000000001' }` works as is. ## Count elements `isEmpty` and `length` compile to `cardinality()`. `length` takes a number or a range: **better-drizzle** ```ts const untagged = await client.posts.findMany({ where: { tags: { isEmpty: true } } }); const tagged = await client.posts.findMany({ where: { tags: { isEmpty: false } } }); await client.posts.findMany({ where: { tags: { length: 3 } } }); await client.posts.findMany({ where: { tags: { length: { gte: 2, lte: 5 } } } }); ``` **Drizzle** ```ts import { and, sql } from 'drizzle-orm'; const untagged = await db.select().from(posts).where(sql`cardinality(${posts.tags}) = 0`); const tagged = await db.select().from(posts).where(sql`cardinality(${posts.tags}) > 0`); await db.select().from(posts).where(sql`cardinality(${posts.tags}) = ${3}`); await db .select() .from(posts) .where( and( sql`cardinality(${posts.tags}) >= ${2}`, sql`cardinality(${posts.tags}) <= ${5}`, ), ); ``` ## Negate and combine `not` takes a value, `null`, or a nested array filter. Every operator in one object must hold, and `AND` / `OR` / `NOT` work across columns as usual: ```ts await client.posts.findMany({ where: { tags: { not: ['draft'] } } }); // not this exact array await client.posts.findMany({ where: { scores: { not: null } } }); // has a value at all await client.users.findMany({ where: { roles: { not: { has: 'admin' } } } }); await client.posts.findMany({ where: { tags: { has: 'drizzle', hasNone: ['deprecated'], length: { lte: 5 } }, OR: [{ editors: { hasSome: [currentUserId] } }, { roles: { has: 'viewer' } }], }, }); ``` ## Use in relations and other helpers Array filters work anywhere a `where` is accepted, including relation filters, `include` filters, `count`, `exists`, `paginate`, and bulk writes: ```ts const authors = await client.users.findMany({ where: { posts: { some: { tags: { has: 'drizzle' } } } }, include: { posts: { where: { tags: { hasSome: ['drizzle', 'postgres'] } } } }, }); const drizzlePosts = { tags: { has: 'drizzle' } } as const; const total = await client.posts.count({ where: drizzlePosts }); const { count } = await client.posts.updateMany({ where: drizzlePosts, data: { flags: ['featured'] }, }); ``` ## Filter individual elements Use `some`, `every`, and `none` when the question is about a predicate on an individual element rather than exact membership. Their nested filter is typed from the array element: numeric arrays expose comparisons, string arrays expose patterns and `mode`, and enum arrays keep their literal values. **better-drizzle** ```ts const rows = await client.posts.findMany({ where: { scores: { some: { gt: 100 }, every: { gte: 0 } }, tags: { some: { startsWith: 'drizzle', mode: 'insensitive' } }, flags: { none: { equals: 'archived' } }, }, }); ``` **Drizzle** ```ts import { sql } from 'drizzle-orm'; const rows = await db .select() .from(posts) .where(sql` ${posts.scores} is not null and exists (select 1 from unnest(${posts.scores}) as array_element where (array_element > ${100}) is true) and not exists (select 1 from unnest(${posts.scores}) as array_element where (array_element >= ${0}) is not true) and exists (select 1 from unnest(${posts.tags}) as array_element where array_element ilike ${'drizzle%'}) and ${posts.flags} is not null and not (${posts.flags} @> array[${'archived'}]) `); ``` An empty array has no matching element, so `some` is false while `every` and `none` are true. A `NULL` array never matches these operators. A `NULL` item does not satisfy the nested predicate: it fails `every`, is ignored by `some`, and does not prevent `none`. For `some` equality/list predicates and `every` equality/list predicates, better-drizzle emits PostgreSQL array operators (`@>`, `&&`, `<@`) that a GIN index can use. `none` is a negated containment/overlap condition, so the normal GIN operator class cannot narrow it selectively. Comparisons, patterns, compound filters, and nested `not` use `unnest()`; they also cannot use that GIN path. Use a normalized child table when those predicates are a primary access path on a large dataset. ## Edge cases **NULL arrays never match an operator** A `NULL` array is not an empty array. `has`, `hasEvery`, `hasSome`, `hasNone`, `containedBy`, `isEmpty`, `length`, and a nested `not` all evaluate to SQL `NULL` for it, so the row is left out, even for `hasNone` and `isEmpty: false`. Match `NULL` explicitly with `equals: null` (or `null`) and exclude it with `not: null`: ```ts await client.posts.findMany({ where: { scores: null } }); // IS NULL await client.posts.findMany({ where: { scores: { not: null } } }); // IS NOT NULL ``` **Containment ignores order and duplicates** `has`, `hasEvery`, `hasSome`, `hasNone`, and `containedBy` treat the array as a set, as PostgreSQL does: `['a', 'b']` contains `['b', 'a']`, and duplicates do not change the result. Exact equality (a plain array or `equals`) compares order and duplicates too. **Empty lists** An empty list is valid and follows PostgreSQL: | Filter | Matches | | ----------------- | ---------------------- | | `hasEvery: []` | every non-`NULL` array | | `hasSome: []` | nothing | | `hasNone: []` | every non-`NULL` array | | `containedBy: []` | only empty arrays | **length counts every element** `length` and `isEmpty` use PostgreSQL `cardinality()`, which counts all elements across every dimension, so a 2x3 array has a length of 6. Duplicates are counted. ## Reference | Operator | Matches rows where the array... | SQL | | ------------- | ------------------------------------------------------------------------ | --------------------------------- | | a plain array | equals the value exactly | `column = $1` | | `equals` | equals the value exactly (`null` matches `IS NULL`) | `column = $1` | | `has` | contains this element | `column @> ARRAY[$1]` | | `hasEvery` | contains every listed element | `column @> $1` | | `hasSome` | contains at least one listed element | `column && $1` | | `hasNone` | contains none of the listed elements | `NOT (column && $1)` | | `containedBy` | has only elements from the list | `column <@ $1` | | `isEmpty` | is empty (`true`) or has at least one element (`false`) | `cardinality(column) = 0` / `> 0` | | `length` | has this many elements, or a range | `cardinality(column) = $1` | | `not` | does not equal a value, is not `null`, or does not match a nested filter | `NOT (...)` | `length` accepts a number or the comparable operators: `equals`, `in`, `notIn`, `lt`, `lte`, `gt`, `gte`, and `not`. Operators in the same filter object are combined with `AND`. ## Validation with Zod The [Zod plugin](/docs/plugins/zod) understands array columns: create and update schemas expect arrays of the element type, and `where` schemas accept the array filter operators with the same element type. ## Limits and dialect support * **PostgreSQL only.** An array filter object on SQLite or MySQL throws `ARRAY_QUERY_UNSUPPORTED` (status 400). Plain equality with an array value still goes through the regular filter path. * **Native array columns only.** JSON columns typed as arrays use the [JSONB filters](/docs/querying/jsonb) instead. * **Element predicates.** Containment operators compare whole elements for equality. Predicates on individual elements, such as "any score above 10", use [`some` / `every` / `none`](#filter-individual-elements). * **No ordering by array contents.** `orderBy` takes scalar columns.