Comparison Operators

UQL provide a comprehensive set of operators for comparing field values. These operators are context-aware, meaning they are typed according to the field they are applied to.

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.
`$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. E.g. `{ addresses: { $elemMatch: { city: 'NYC' } } }`.
`$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, and SQLite.

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

Comparison Operators