Comparison Operators

UQL’s comparison operators are context-aware: each operator is typed according to the field it is applied to, and only the operators applicable to a field’s type are accepted:

Every field: $eq, $ne, $not, $in, $nin, $isNull, $isNotNull (a bare array is an implicit $in on scalar fields).
Comparable fields (string, number, bigint, Date): $lt, $lte, $gt, $gte, $between.
String fields: $like, $ilike, $regex, $startsWith, $istartsWith, $endsWith, $iendsWith, $includes, $iincludes.
Array fields: $all, $size, $elemMatch.

An inapplicable combination - { age: { $like: '3%' } }, { active: { $gt: false } }, { name: { $size: 3 } } - is a compile error.

Name	Description
`$eq`	Equal to.
`$ne`	Not equal to (null-safe: rows where the column is `NULL` still match when the value is not null).
`$lt`	Less than.
`$lte`	Less than or equal to.
`$gt`	Greater than.
`$gte`	Greater than or equal to.
`$like`	SQL `LIKE` pattern match (case sensitive). E.g. `{ name: { $like: 'John%' } }`.
`$ilike`	SQL `ILIKE` pattern match (case insensitive). E.g. `{ name: { $ilike: 'john%' } }`.
`$regex`	Regular expression match. E.g. `{ name: { $regex: '^test' } }`.
`$startsWith`	Starts with (case-sensitive).
`$istartsWith`	Starts with (case-insensitive).
`$endsWith`	Ends with (case-sensitive).
`$iendsWith`	Ends with (case-insensitive).
`$includes`	Contains substring (case-sensitive).
`$iincludes`	Contains substring (case-insensitive).
`$in`	Value matches any in a given array.
`$nin`	Value does not match any in a given array.
`$between`	Value is between two bounds (inclusive). E.g. `{ age: { $between: [18, 65] } }`.
`$isNull`	Field is null. E.g. `{ deletedAt: { $isNull: true } }`.
`$isNotNull`	Field is not null. E.g. `{ email: { $isNotNull: true } }`.
`$all`	Array contains all specified values. E.g. `{ tags: { $all: ['ts', 'orm'] } }`.
`$size`	Array has the specified length. Accepts a number for exact match (`{ tags: { $size: 3 } }`) or comparison operators (`{ tags: { $size: { $gte: 2 } } }`). Also works on relation count filtering.
`$elemMatch`	Array contains an element matching the condition. Object elements take per-key conditions (`{ addresses: { $elemMatch: { city: 'NYC' } } }`); scalar elements take an operator map (`{ tags: { $elemMatch: { $startsWith: 'ad' } } }`).
`$text`	Full-text search (where supported by the database).

Practical Example

import { User } from './shared/models/index.js';

const users = await querier.findMany(User, {
  $select: { id: true, name: true },
  $where: {
    name: { $istartsWith: 'Some', $ne: 'Something' },
    age: { $gte: 18, $lte: 65 }
  },
  $sort: { name: 'asc' },
  $limit: 50
});

Context-Aware SQL Generation

UQL transparently handles the differences between database vendors. For example, $istartsWith is translated to ILIKE in PostgreSQL, but to LOWER(field) LIKE 'some%' in MySQL.

SELECT "id", "name" FROM "User"
WHERE ("name" ILIKE $1 AND "name" IS DISTINCT FROM $2) AND ("age" >= $3 AND "age" <= $4)
ORDER BY "name"
LIMIT 50

SELECT `id`, `name` FROM `User`
WHERE (LOWER(`name`) LIKE ? AND NOT (`name` <=> ?)) AND (`age` >= ? AND `age` <= ?)
ORDER BY `name`
LIMIT 50

SELECT `id`, `name` FROM `User`
WHERE (`name` LIKE ? AND `name` IS NOT ?) AND (`age` >= ? AND `age` <= ?)
ORDER BY `name`
LIMIT 50

`$between`: Range Queries

const users = await querier.findMany(User, {
  $where: { age: { $between: [18, 65] } },
});

SELECT * FROM "User" WHERE "age" BETWEEN $1 AND $2

JSONB Dot-Notation Operators

All comparison operators listed above also work on nested JSON field paths using dot-notation (e.g., 'settings.isArchived': { $ne: true }). UQL generates dialect-specific SQL automatically across PostgreSQL, MySQL, MariaDB, and SQLite.

See the dedicated JSON / JSONB page for full documentation including filtering, $merge/$unset update operators, and sorting by JSON paths.

Comparison Operators