Logical Operators

Logical operators combine comparison conditions in $where, nested to any depth:

Operator	Matches rows where
$and	every clause matches (the default)
$or	at least one clause matches
$not	the clauses do not all match
$nor	no clause matches

You write

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

const users = await pool.findMany(User, {
  $where: {
    $or: [
      { name: { $istartsWith: 'A' } },
      { status: 'pending', createdAt: { $lt: new Date('2025-01-01') } },
    ],
  },
});

PostgreSQL

SELECT * FROM "User"
WHERE "name" ILIKE $1 ESCAPE '\' OR ("status" = $2 AND "createdAt" < $3)

Fields in one object are joined with AND, as in the second clause above, so $and: [{ name: 'roger' }, { status: 'active' }] and { name: 'roger', status: 'active' } are the same query. Write $and to combine two $or groups, or to add a sql condition.

$not and $nor: negate a condition

On a field, $not negates the conditions inside it:

You write

const users = await pool.findMany(User, {
  $where: { status: 'active', name: { $not: { $startsWith: 'test' } } },
});

PostgreSQL

SELECT * FROM "User" WHERE "status" = $1 AND NOT ("name" LIKE $2 ESCAPE '\')

At the top level, $not takes an array and negates its clauses joined with AND, so a row matches unless every clause does. $nor negates them joined with OR, so a row matches only if no clause does:

You write

const users = await pool.findMany(User, {
  $where: { $nor: [{ name: 'admin' }, { status: 'banned' }] },
});

PostgreSQL

SELECT * FROM "User" WHERE NOT ("name" = $1 OR "status" = $2)
-- with $not: WHERE NOT ("name" = $1 AND "status" = $2)

On SQL, a negation leaves out rows where the compared field is NULL; MongoDB keeps them. See NULL per engine.