filters.md

Filter Rules

Comprehensive guide for building ObjectStack query filters.

Operator Reference

Category	Operator	SQL Equivalent	Example
Equality	$eq	=	{ status: { $eq: 'active' } }
Equality	$ne	<>	{ status: { $ne: 'deleted' } }
Comparison	$gt	>	{ age: { $gt: 18 } }
Comparison	$gte	>=	{ amount: { $gte: 100 } }
Comparison	$lt	<	{ price: { $lt: 50 } }
Comparison	$lte	<=	{ score: { $lte: 100 } }
Set	$in	IN (...)	{ status: { $in: ['active', 'pending'] } }
Set	$nin	NOT IN (...)	{ role: { $nin: ['guest'] } }
Range	$between	BETWEEN ? AND ?	{ age: { $between: [18, 65] } }
String	$contains	LIKE %?%	{ name: { $contains: 'john' } }
String	$notContains	NOT LIKE %?%	{ email: { $notContains: 'spam' } }
String	$startsWith	LIKE ?%	{ code: { $startsWith: 'PRJ-' } }
String	$endsWith	LIKE %?	{ file: { $endsWith: '.pdf' } }
Null	$null	IS NULL / IS NOT NULL	{ deleted_at: { $null: true } }
Existence	$exists	(NoSQL) $exists	{ metadata: { $exists: true } }

Implicit Equality (Shorthand)

The most common filter — equality — has a shorthand:

// ✅ Implicit equality (preferred for simple cases)
where: { status: 'active' }

// ✅ Explicit equality (same result)
where: { status: { $eq: 'active' } }

Logical Operators

AND (implicit)

All top-level conditions are AND-combined by default:

// ✅ Implicit AND — all conditions must match
where: {
  status: 'active',
  role: 'admin',
  age: { $gte: 18 }
}

// ✅ Explicit $and — same result
where: {
  $and: [
    { status: 'active' },
    { role: 'admin' },
    { age: { $gte: 18 } }
  ]
}

OR

// ✅ Find admins OR managers
where: {
  $or: [
    { role: 'admin' },
    { role: 'manager' }
  ]
}

// ✅ Equivalent using $in
where: {
  role: { $in: ['admin', 'manager'] }
}

NOT

// ✅ Exclude deleted records
where: {
  $not: { status: 'deleted' }
}

Combining Logical Operators

// ✅ Active users who are admin OR have high score
where: {
  status: 'active',            // AND
  $or: [
    { role: 'admin' },
    { score: { $gte: 90 } }
  ]
}

Field References

⚠️ $field is schema-reserved — NOT executed by the engine yet. It exists only in the filter schema; no engine or driver code interprets it, so the { $field: '...' } object binds as a literal value and the query silently returns zero rows.

// ❌ Schema-valid but NOT executed — matches nothing
where: {
  actual_cost: { $gt: { $field: 'budget' } }
}

Working alternatives:

• Define a formula field that computes the cross-field comparison (e.g. a boolean over_budget), then filter on it: where: { over_budget: true } (see objectstack-data).
• Fetch both fields and compare in application code.

Nested Relation Filters

Filter by a related object's fields:

// ✅ Find orders where the customer is in the US
where: {
  customer: {
    country: 'US'
  }
}

// ✅ Deeper nesting
where: {
  customer: {
    organization: {
      industry: 'Technology'
    }
  }
}

Common Mistakes

❌ Wrong: Multiple operators on different fields inside $or

// ❌ This is an AND, not an OR
where: {
  role: 'admin',
  status: 'active'
}
// Correct only if you want both conditions

// ✅ For OR, wrap in $or array
where: {
  $or: [
    { role: 'admin' },
    { status: 'active' }
  ]
}

❌ Wrong: Using string operators on non-string fields

// ❌ $contains only works on string fields
where: {
  age: { $contains: '25' }  // age is a number
}

// ✅ Use comparison operators for numbers
where: {
  age: { $eq: 25 }
}

❌ Wrong: Using $between with wrong tuple length

// ❌ $between requires exactly [min, max]
where: {
  price: { $between: [10, 50, 100] }
}

// ✅ Correct: exactly two elements
where: {
  price: { $between: [10, 50] }
}

❌ Wrong: Null check with equality

// ❌ Don't use equality to check for null
where: {
  deleted_at: null
}

// ✅ Use $null operator
where: {
  deleted_at: { $null: true }
}

Date Filtering Patterns

For ad-hoc queries in application code, compute the date yourself:

// Records created in the last 7 days (compute date in application code)
where: {
  created_at: { $gte: new Date('2025-01-01') }
}

// Records within a date range
where: {
  created_at: {
    $between: [new Date('2025-01-01'), new Date('2025-03-31')]
  }
}

Date Macros (declarative filter metadata)

Filters that travel as JSON metadata — list views, dashboards, reports, pages — cannot run code, so they use date macro tokens instead (defined in @objectstack/spec data/date-macros.zod.ts):

// List-view / dashboard filter values
where: {
  signal_at:    { $gte: '{30_days_ago}' },
  published_at: { $gte: '{last_quarter_start}' }
}

• Fixed tokens: {today}, {yesterday}, {tomorrow}, {now}, plus period boundaries like {current_month_start}, {last_quarter_end}, {next_year_start} (and bare aliases like {month_start}).
• Parameterised tokens: {N_<unit>_ago} / {N_<unit>_from_now} with units minute|hour|day|week|month|year — e.g. {30_days_ago}, {2_weeks_from_now}.

Scope: the tokens are expanded client-side (by resolveDateMacros() in @object-ui/core) immediately before the filter reaches the data engine — the engine only ever sees ISO date/timestamp strings. Use macros in UI-rendered filter metadata; for queries you issue directly against the engine, keep computing dates in application code as above.