Comparison Operators

Comparison operators are the conditions you put on a field in $where:

You write

const users = await pool.findMany(User, {
  $where: {
    name: { $istartsWith: 'Some', $ne: 'Something' },
    age: { $gte: 18, $lte: 65 },
  },
});

PostgreSQL

SELECT * FROM "User"
WHERE ("name" ILIKE $1 ESCAPE '\' AND "name" <> $2) AND ("age" >= $3 AND "age" <= $4)
-- values: ['Some%', 'Something', 18, 65]

MySQL / MariaDB

SELECT * FROM `User`
WHERE (LOWER(`name`) LIKE ? ESCAPE '\\' AND `name` <> ?) AND (`age` >= ? AND `age` <= ?)
-- values: ['some%', 'Something', 18, 65]

Each field accepts only the operators that fit its type, so { age: { $like: '3%' } } or { active: { $gt: false } } is a compile error.

Operator	Fields	Matches
$eq, $ne	any	equal, not equal. { name: 'Ada' } is shorthand for $eq.
$in, $nin	any	any, or none, of the values. A bare array is an implicit $in.
$isNull, $isNotNull	any	{ deletedAt: { $isNull: true } }
$not	any	the negation of the conditions inside it
$lt, $lte, $gt, $gte	string, number, bigint, Date	less than, greater than, with or without equal
$between	string, number, bigint, Date	between two bounds, both included: { $between: [18, 65] }
$like, $ilike	string	a LIKE pattern over the whole string
$startsWith, $endsWith, $includes	string	the text at the start, at the end, or anywhere
$regex	string	a regular expression, case-sensitive unless it opens with (?i)
$all	array	holds every value
$size	array	has that length: 3, or { $gte: 2 }
$elemMatch	array	some element matches the condition

Each text operator has a case-insensitive i form: $ilike, $istartsWith, $iendsWith, $iincludes. Full-text search is $text, which goes at the top of $where rather than on a field; see full-text search.

Text matching

$like patterns read the same on every engine: % is any run of characters, _ is one character, and \ makes the next character literal ('50\%' matches 50%). A pattern ending in a lone \ is refused. $startsWith, $endsWith and $includes take their text literally, so % and _ are plain characters there.

A leading (?i) makes $regex ignore case, as MongoDB’s $options: 'i' does. An embedded libSQL file refuses the flag, since its REGEXP is SQLite’s own; a libSQL or Turso server takes it. $regex needs SQL Server 2025 at compatibility level 170, and does not run on bun:sqlite, which has no REGEXP.

The i operators match the same rows everywhere, by different means. PostgreSQL and CockroachDB use ILIKE. SQLite’s LIKE already ignores case, for ASCII letters only. MySQL, MariaDB and SQL Server lower both sides, as the MySQL tab above shows, so no plain index serves $istartsWith; on MySQL an expression index over LOWER(column) can. Their default collation already ignores case, though, so there $startsWith matches either case and keeps its plain index. On PostgreSQL it stays case-sensitive.

Array matching

$all compares scalars by JSON type, so '5' does not match 5; an object or array value matches by what it holds.

$elemMatch takes a value or operator map per key on object elements, as in { addresses: { $elemMatch: { city: 'NYC', zip: { $startsWith: '10' } } } }. On scalar elements it takes an operator map, as in { tags: { $elemMatch: { $startsWith: 'ad' } } }, where a single $eq or $in compares by JSON type, as $all does.

$size also filters by a relation’s size. To return or sort by that size, see counting.

Comparing with another column

Wherever an operator takes a value, it takes a ref to another column of the same row in its place, $between, $in and $ne included, on every engine, MongoDB too:

import { Entity, f, Field, refs } from 'uql-orm';

@Entity()
export class Rate {
  @Field(f.number().autoIncrement()) id?: number;
  @Field(f.date()) effectiveFrom!: Date;
  @Field(f.date().nullable()) effectiveTo?: Date | null;
}

const rate = refs(Rate);

await pool.findMany(Rate, {
  $where: { effectiveTo: { $gt: rate.effectiveFrom } },
});

A JSON path compared with a ref reads as that column’s type. A ref names a column of the entity the query is on: one of another entity is refused, and is read off its own alias, refs(Other, 'o'), in raw SQL.

NULL is compared as the engine compares it

In SQL, NULL <> 'a' is neither true nor false, so every negation leaves a NULL row out. MongoDB compares a missing or null field as a value, and keeps it:

You write	SQL, code is NULL	MongoDB, code missing or null
{ code: { $ne: 'a' } }	left out	matches
{ code: { $nin: ['a'] } }	left out	matches
{ code: { $in: ['a', null] } }	left out	matches
{ code: { $not: { $eq: 'a' } } }, { $nor: [{ code: 'a' }] }	left out	matches

For the same rows everywhere, name NULL yourself: { $or: [{ code: { $ne: 'a' } }, { code: null }] }. { code: null } and { code: { $ne: null } } mean “is null” and “is not null” on every engine.

undefined throws

An undefined value anywhere in $where or $having throws, because { email: maybeEmail } would otherwise match every row. Leave the key out, or pass null.

JSONB dot-notation operators

The operators also work on paths inside a JSON column, such as 'settings.isArchived': { $ne: true }. See JSON / JSONB.

Next steps

• Logical operators: combine conditions with $and, $or, $not and $nor.
• Relations: filter across related entities.