Complete Filter Reference

Note: Exhaustive operator list and syntax. For practical patterns, see Advanced Query Guide. For parameter usage in requests, see Item Query Reference Guide.

← Back to Documentation Home

Filter Syntax Overview
$ Field Qualifier System
Basic Operators
String Pattern Operators
Array Operators
Range Operators
Null Check Operators
Geospatial Operators
Logical Operators
Type Casting
Column-to-Column Comparisons
Relational Field Filtering
RelCondition System
Dynamic Variables
Relative Date Variables
Complete Examples

Filter Syntax Overview

BAASIX filters use a JSON-based syntax with powerful operators and special field qualifiers. All filters follow the same structure whether used in GET parameters or POST body.

Basic Filter Structure

{
  "filter": {
    "fieldName": "value",  // Simple equality
    "fieldName": {"operator": "value"},  // With operator
    "qualifiedField": {"operator": "value"}  // With field qualifiers
  }
}

NEW: BAASIX now supports both plain field names and $fieldName$ syntax for field qualification. The plain syntax is recommended for clarity, but the $ delimiter syntax is still fully supported for backward compatibility.

Field Qualifier Rules

// ✅ RECOMMENDED: Use plain field names (new style)
{
  "title": {"like": "BAASIX"},
  "author.name": {"eq": "John"},
  "comments.approved": {"eq": true}
}

// ✅ ALSO SUPPORTED: Use $ qualifiers (legacy style, still works)
{
  "title": {"like": "BAASIX"},
  "author.name": {"eq": "John"},
  "comments.approved": {"eq": true}
}

// ✅ MIXING FORMATS: Both formats can be used together
{
  "title": {"like": "BAASIX"},       // Plain format
  "author.name": {"eq": "John"}      // Also plain format
}

Field Reference Syntax

Both syntaxes are supported:

Plain field names: fieldName or relation.field (recommended)
Legacy $ qualifiers: $fieldName$ or $relation.field$ (backward compatible)

Examples:

// Simple field (both work)
{"status": "published"}           // Recommended
{"status": "published"}           // Legacy (still works)

// Relational field (both work)
{"author.name": {"eq": "John"}}   // Recommended
{"author.name": {"eq": "John"}}   // Legacy (still works)

// Deep nested (both work)
{"comments.user.profile.verified": {"eq": true}}   // Recommended
{"comments.user.profile.verified": {"eq": true}}   // Legacy (still works)

Basic Operators

Equality and Comparison

Operator	Description	Example
`eq`	Equal (default)	`{"price": {"eq": 100}}`
`ne`	Not equal	`{"status": {"ne": "draft"}}`
`gt`	Greater than	`{"price": {"gt": 100}}`
`gte`	Greater than or equal	`{"price": {"gte": 100}}`
`lt`	Less than	`{"views": {"lt": 1000}}`
`lte`	Less than or equal	`{"views": {"lte": 1000}}`

Collection Operators

Operator	Description	Example
`in`	Value in array	`{"status": {"in": ["published", "featured"]}}`
`notIn`	Value not in array	`{"category": {"notIn": ["spam", "deleted"]}}`
`not`	Not equal (alternative)	`{"status": {"not": "draft"}}`
`is`	SQL IS operator	`{"deletedAt": {"is": null}}`

// Examples
{
  "price": {"gte": 10, "lte": 100},  // Between 10 and 100
  "tags": {"in": ["javascript", "nodejs", "api"]},
  "status": {"notIn": ["deleted", "spam"]}
}

String Pattern Operators

Pattern Matching (Auto-wrapped with %)

IMPORTANT: like and ilike automatically add % wildcards - do NOT include them in your values.

Operator	Description	Auto-wrapping	Example
`like`	Case-sensitive pattern	`%value%`	`{"title": {"like": "BAASIX"}}` → `%BAASIX%`
`notLike`	NOT LIKE pattern	`%value%`	`{"title": {"notLike": "spam"}}` → `%spam%`
`iLike`	Case-insensitive pattern	`%value%`	`{"email": {"iLike": "gmail.com"}}` → `%gmail.com%`
`notILike`	NOT ILIKE pattern	`%value%`	`{"email": {"notILike": "spam"}}` → `%spam%`

Prefix/Suffix Matching

Operator	Description	Pattern	Example
`startsWith`	Case-insensitive starts with	`value%`	`{"name": {"startsWith": "John"}}`
`startsWiths`	Case-sensitive starts with	`value%`	`{"code": {"startsWiths": "PRD"}}`
`endsWith`	Case-insensitive ends with	`%value`	`{"email": {"endsWith": ".com"}}`
`endsWiths`	Case-sensitive ends with	`%value`	`{"filename": {"endsWiths": ".jpg"}}`
`nstartsWith`	NOT starts with (insensitive)	`value%`	`{"name": {"nstartsWith": "test"}}`
`nstartsWiths`	NOT starts with (sensitive)	`value%`	`{"code": {"nstartsWiths": "TMP"}}`
`nendsWith`	NOT ends with (insensitive)	`%value`	`{"email": {"nendsWith": ".tmp"}}`
`nendsWiths`	NOT ends with (sensitive)	`%value`	`{"filename": {"nendsWiths": ".tmp"}}`

// ✅ CORRECT: No % needed - automatically added
{
  "title": {"like": "BAASIX"},        // Matches %BAASIX%
  "email": {"iLike": "gmail.com"},    // Matches %gmail.com%
  "name": {"startsWith": "John"},     // Matches John%
  "email": {"endsWith": ".edu"}       // Matches %.edu
}

// ❌ INCORRECT: Don't add % manually
{
  "title": {"like": "%BAASIX%"},  // Results in %%BAASIX%%
  "name": {"startsWith": "John%"} // Results in John%%
}

Array Operators

BAASIX supports PostgreSQL array operations with proper type handling.

Array Containment Operators

Operator	PostgreSQL	Description	Example
`arraycontains`	`@>`	Array contains elements	`{"tags": {"arraycontains": ["javascript", "api"]}}`
`arraycontained`	`<@`	Array is contained by	`{"permissions": {"arraycontained": ["read", "write", "admin"]}}`

Array Type Support

BAASIX automatically handles different array types:

// String arrays
{
  "tags": {"arraycontains": ["javascript", "nodejs"]}
}

// Integer arrays
{
  "scores": {"arraycontains": [85, 90, 95]}}
}

// UUID arrays
{
  "userIds": {"arraycontains": ["uuid1", "uuid2"]}}
}

// Decimal arrays
{
  "prices": {"arraycontains": [19.99, 29.99]}}
}

// Boolean arrays
{
  "flags": {"arraycontains": [true, false]}}
}

Array Operators in Relationships

// Single-level relationship
{
  "author.specialties": {"arraycontains": ["javascript", "python"]}
}

// Multi-level nested relationship
{
  "employee.department.company.technologies": {
    "arraycontains": ["nodejs", "postgresql"]
  }
}

Range Operators

Operator	Description	Value Format	Example
`between`	SQL BETWEEN	`[min, max]`	`{"price": {"between": [10, 100]}}`
`notBetween`	SQL NOT BETWEEN	`[min, max]`	`{"age": {"notBetween": [13, 17]}}`

// Range examples
{
  "price": {"between": [10.00, 100.00]},
  "createdAt": {"between": ["2025-01-01", "2025-12-31"]},
  "score": {"notBetween": [0, 50]}  // Exclude failing scores
}

Null Check Operators

Operator	Description	Value	Result
`isNull`	IS NULL check	`true`	Field IS NULL
`isNull`	IS NOT NULL check	`false`	Field IS NOT NULL
`isNotNull`	IS NOT NULL check	`true`	Field IS NOT NULL
`isNotNull`	IS NULL check	`false`	Field IS NULL

// Null checks
{
  "deletedAt": {"isNull": true},      // Not deleted items
  "publishedAt": {"isNotNull": true}, // Published items
  "email": {"isNull": false},         // Has email
  "phone": {"isNotNull": false}       // No phone number
}

Geospatial Operators

BAASIX supports PostGIS spatial operations:

Basic Spatial Operators

Operator	PostGIS Function	Description	Example
`within`	`ST_Within`	Geometry is within another	Point within polygon
`containsGEO`	`ST_Contains`	Geometry contains another	Polygon contains point
`intersects`	`ST_Intersects`	Geometries intersect	Line crosses polygon
`nIntersects`	`NOT ST_Intersects`	Geometries don't intersect	Line doesn't cross polygon
`dwithin`	`ST_DWithin`	Within distance	Point within radius

Geospatial Examples

// Point within polygon
{
  "location": {
    "within": {
      "type": "Polygon",
      "coordinates": [[[lng1,lat1], [lng2,lat2], [lng3,lat3], [lng1,lat1]]]
    }
  }
}

// Point within distance (radius search)
{
  "location": {
    "dwithin": {
      "geometry": {
        "type": "Point",
        "coordinates": [-74.0060, 40.7128]  // NYC
      },
      "distance": 5000,  // 5km radius
      "not": false       // Optional: negate condition
    }
  }
}

// Polygon contains point
{
  "boundary": {
    "containsGEO": {
      "type": "Point",
      "coordinates": [-74.0060, 40.7128]
    }
  }
}

// Check intersection
{
  "route": {
    "intersects": {
      "type": "LineString",
      "coordinates": [[lng1,lat1], [lng2,lat2]]
    }
  }
}

// Exclude intersecting geometries
{
  "route": {
    "nIntersects": {
      "type": "Polygon",
      "coordinates": [[[lng1,lat1], [lng2,lat2], [lng3,lat3], [lng1,lat1]]]
    }
  }
}

Logical Operators

AND/OR Operators

// Explicit AND (default behavior)
{
  "AND": [
    {"status": {"eq": "published"}},
    {"views": {"gt": 100}}
  ]
}

// OR operator
{
  "OR": [
    {"status": {"eq": "featured"}},
    {"views": {"gt": 1000}}
  ]
}

// Nested logical operators
{
  "AND": [
    {
      "OR": [
        {"status": {"eq": "published"}},
        {"status": {"eq": "featured"}}
      ]
    },
    {
      "AND": [
        {"views": {"gt": 100}},
        {"createdAt": {"gt": "2025-01-01"}}
      ]
    }
  ]
}

// Mixed implicit and explicit
{
  "category": {"eq": "tech"},  // Implicit AND
  "OR": [
    {"status": {"eq": "published"}},
    {"featured": {"eq": true}}
  ]
}

Type Casting

BAASIX supports type casting in filter operations to convert field values before comparison. Add the "cast" parameter to any filter condition.

Supported Cast Types

Cast Type	Description	Use Case
`text`	Convert to text/string	Pattern matching on numbers
`varchar`	Convert to varchar	String operations
`integer`	Convert to integer	Numeric comparisons
`bigint`	Convert to big integer	Large number operations
`decimal`	Convert to decimal	Precise numeric operations
`boolean`	Convert to boolean	Boolean comparisons
`date`	Extract date part	Date-only comparisons from datetime
`time`	Extract time part	Time-only comparisons from datetime
`timestamp`	Convert to timestamp	Timestamp operations
`uuid`	Convert to UUID	UUID operations
`json`	Convert to JSON	JSON operations
`jsonb`	Convert to JSONB	JSONB operations

Casting Examples

Date/Time Extraction

// Extract date part from datetime field
{
  "createdAt": {"eq": "2025-01-15", "cast": "date"}
}

// Extract time part from datetime field
{
  "workStartTime": {"between": ["08:00:00", "18:00:00"], "cast": "time"}
}

// Date range filtering
{
  "birthDate": {"between": ["1990-01-01", "2000-12-31"], "cast": "date"}
}

Number to Text Conversion

// Pattern matching on numeric fields
{
  "age": {"like": "2", "cast": "text"}  // Ages containing "2" (25, 32, etc.)
}

// Starts with pattern on prices
{
  "price": {"startsWith": "199", "cast": "text"}  // Prices starting with 199
}

Boolean Conversion

// Convert to text for string operations
{
  "isActive": {"eq": "true", "cast": "text"}
}

Relational Field Casting

// Cast fields in related tables
{
  "department.establishedDate": {"eq": "2020-01-15", "cast": "date"},
  "department.budget": {"like": "500000", "cast": "text"}
}

Complex Casting with Logical Operators

{
  "AND": [
    {"workStartTime": {"between": ["08:00:00", "18:00:00"], "cast": "time"}},
    {"salary": {"startsWith": "7", "cast": "text"}},
    {"createdAt": {"gte": "2025-01-01", "cast": "date"}}
  ]
}

Error Handling

Invalid cast types: Ignored gracefully, query proceeds without casting
NULL values: Handled properly in cast operations
Type mismatches: PostgreSQL handles conversion errors gracefully

Column-to-Column Comparisons

BAASIX supports comparing field values to other field values (column-to-column comparisons) using the $COL() syntax. This is useful for scenarios like comparing dates, validating business rules, or finding data inconsistencies.

Basic Column Comparison Syntax

Use $COL(columnName) to reference another column in the same record:

// Find events where start time is greater than end time (invalid events)
{
  "startTime": {"gt": "$COL(endTime)"}
}

// Find projects where actual cost exceeds estimated cost
{
  "actualCost": {"gt": "$COL(estimatedCost)"}
}

// Find users where last login is the same as registration date
{
  "lastLoginAt": {"eq": "$COL(registeredAt)"}
}

Supported Operators for Column Comparisons

Operator	Description	Example
`eq`	Equal to another column	`{"fieldA": {"eq": "$COL(fieldB)"}}`
`ne`	Not equal to another column	`{"fieldA": {"ne": "$COL(fieldB)"}}`
`gt`	Greater than another column	`{"fieldA": {"gt": "$COL(fieldB)"}}`
`gte`	Greater than or equal to another column	`{"fieldA": {"gte": "$COL(fieldB)"}}`
`lt`	Less than another column	`{"fieldA": {"lt": "$COL(fieldB)"}}`
`lte`	Less than or equal to another column	`{"fieldA": {"lte": "$COL(fieldB)"}}`

Column Comparisons with Type Casting

Standard Casting Approach

Apply casting to both columns using the cast parameter:

// Compare time parts of datetime fields
{
  "workStartTime": {
    "gt": "$COL(workEndTime)",
    "cast": "time"
  }
}

// Compare date parts only, ignoring time
{
  "createdAt": {
    "eq": "$COL(updatedAt)",
    "cast": "date"
  }
}

// Cast numbers to text for pattern matching
{
  "employeeId": {
    "like": "$COL(managerId)",
    "cast": "text"
  }
}

PostgreSQL Casting Syntax in $COL()

You can also specify casting directly within the $COL() reference using PostgreSQL syntax:

// Cast the referenced column to time for comparison
{
  "startTime": {
    "gt": "$COL(endTime::time)",
    "cast": "time"
  }
}

// Cast referenced column to date
{
  "birthDate": {
    "eq": "$COL(hireDate::date)"
  }
}

// Multiple casting options
{
  "price": {
    "like": "$COL(cost::text)",
    "cast": "text"
  }
}

Relational Column Comparisons

Compare fields across relationships:

// Employee salary vs department minimum salary
{
  "salary": {"gt": "$COL(department.minSalary)"}
}

// Order total vs customer credit limit
{
  "totalAmount": {"lte": "$COL(customer.creditLimit)"}
}

// Product price vs category average price
{
  "price": {"lt": "$COL(category.averagePrice)"}
}

Complex Column Comparison Examples

Business Rule Validation

// Find orders where discount exceeds maximum allowed
{
  "discountAmount": {"gt": "$COL(maxDiscount)"}
}

// Find employees earning more than their manager
{
  "salary": {"gt": "$COL(manager.salary)"}
}

// Find events that end before they start (data validation)
{
  "endDate": {"lt": "$COL(startDate)"}
}

Time-based Comparisons

// Find users who logged in on the same day they registered
{
  "lastLoginAt": {
    "eq": "$COL(createdAt)",
    "cast": "date"
  }
}

// Find shifts where break time exceeds work time
{
  "breakDuration": {"gt": "$COL(workDuration)"}
}

// Compare working hours across different time zones
{
  "localStartTime": {
    "gt": "$COL(utcStartTime::time)",
    "cast": "time"
  }
}

Combined with Logical Operators

{
  "AND": [
    {"actualCost": {"gt": "$COL(estimatedCost)"}},
    {"actualHours": {"lt": "$COL(estimatedHours)"}},
    {"status": {"eq": "completed"}}
  ]
}

{
  "OR": [
    {"endDate": {"lt": "$COL(startDate)"}},
    {"capacity": {"lt": "$COL(currentAttendees)"}}
  ]
}

Error Handling

Invalid column references: Non-existent columns are ignored
Type mismatches: PostgreSQL handles type conversion automatically when possible
NULL values: Handled gracefully in comparisons
Casting errors: Invalid cast types fall back to no casting

Relational Field Filtering

Single-Level Relations

// BelongsTo relationship
{
  "author.name": {"like": "John"},
  "author.status": {"eq": "active"},
  "category.name": {"in": ["tech", "business"]}
}

// HasMany relationship
{
  "comments.approved": {"eq": true},
  "tags.name": {"arraycontains": ["javascript"]}
}

Multi-Level (Deep) Relations

// 3+ levels deep
{
  "comments.user.profile.verified": {"eq": true},
  "author.department.company.status": {"eq": "active"},
  "orderItems.product.category.name": {"like": "electronics"}
}

// Array fields in deep relations
{
  "employee.department.company.technologies": {
    "arraycontains": ["nodejs", "postgresql"]
  }
}

Polymorphic Relations (M2A)

// Comments can belong to Posts or Pages
{
  "related_collection": {"eq": "posts"},
  "commentable.title": {"like": "API"},
  "commentable.author.name": {"eq": "John"}
}

RelCondition System

BAASIX supports a special relCondition syntax for complex relationship filtering that's separate from the main filter.

RelCondition Syntax

{
  "filter": {
    // Regular filters
    "status": {"eq": "published"}
  },
  "relCondition": {
    // Relationship-specific conditions
    "comments": {
      "approved": {"eq": true},
      "createdAt": {"gt": "2025-01-01"}
    },
    "author": {
      "status": {"eq": "active"},
      "profile": {
        "verified": {"eq": true}
      }
    }
  }
}

Deep RelCondition Nesting

{
  "relCondition": {
    "orderItems": {
      "quantity": {"gt": 1},
      "product": {
        "inStock": {"eq": true},
        "category": {
          "active": {"eq": true},
          "featured": {"eq": true}
        }
      }
    }
  }
}

RelCondition with Logical Operators

{
  "relCondition": {
    "comments": {
      "AND": [
        {"approved": {"eq": true}},
        {"flagged": {"eq": false}}
      ]
    },
    "author": {
      "OR": [
        {"verified": {"eq": true}},
        {"reputation": {"gt": 1000}}
      ]
    }
  }
}

Dynamic Variables

BAASIX supports dynamic variables that are resolved at query time:

Available Dynamic Variables

Variable	Description	Example
`$CURRENT_USER`	Current user's ID	`{"authorId": {"eq": "$CURRENT_USER"}}`
`$CURRENT_USER.field`	Current user field	`{"department": {"eq": "$CURRENT_USER.department"}}`
`$CURRENT_ROLE`	Current role's ID	`{"roleId": {"eq": "$CURRENT_ROLE"}}`
`$CURRENT_ROLE.field`	Current role field	`{"permissions": {"arraycontains": "$CURRENT_ROLE.permissions"}}`
`$NOW`	Current timestamp	`{"publishedAt": {"lte": "$NOW"}}`

Dynamic Variable Examples

// User-based filtering
{
  "authorId": {"eq": "$CURRENT_USER"},
  "assignedTo": {"eq": "$CURRENT_USER.id"}
}

// Tenant isolation (multi-tenant)
{
  "tenant_Id": {"eq": "$CURRENT_TENANT"}
}

// Time-based filtering
{
  "publishedAt": {"lte": "$NOW"},
  "expiresAt": {"gt": "$NOW"}
}

// Complex dynamic conditions
{
  "AND": [
    {"authorId": {"eq": "$CURRENT_USER.id"}},
    {"status": {"eq": "published"}},
    {"publishedAt": {"lte": "$NOW"}}
  ]
}

Dynamic Variables in RelConditions

{
  "relCondition": {
    "comments": {
      "authorId": {"eq": "$CURRENT_USER.id"}
    },
    "assignments": {
      "userId": {"eq": "$CURRENT_USER.id"},
      "status": {"eq": "active"}
    }
  }
}

Relative Date Variables

BAASIX supports relative date calculations from the current timestamp using dynamic variables.

Relative Date Pattern

$NOW[+|-][UNIT]_[NUMBER]

Supported Time Units

Unit	Singular	Plural	Description
`YEAR`	`YEAR`	`YEARS`	Calendar years (365 days)
`MONTH`	`MONTH`	`MONTHS`	Calendar months (30 days)
`WEEK`	`WEEK`	`WEEKS`	7-day weeks
`DAY`	`DAY`	`DAYS`	24-hour days
`HOUR`	`HOUR`	`HOURS`	60-minute hours
`MINUTE`	`MINUTE`	`MINUTES`	60-second minutes
`SECOND`	`SECOND`	`SECONDS`	Seconds

Relative Date Examples

Past Dates (Subtraction)

// Records from last 30 days
{
  "createdAt": {"gte": "$NOW-DAYS_30"}
}

// Last week's data
{
  "updatedAt": {"between": ["$NOW-DAYS_7", "$NOW"]}
}

// Items modified in last 2 hours
{
  "lastModified": {"gte": "$NOW-HOURS_2"}
}

Future Dates (Addition)

// Scheduled for next week
{
  "scheduledAt": {"between": ["$NOW", "$NOW+DAYS_7"]}
}

// Expires in next 30 days
{
  "expiresAt": {"lte": "$NOW+DAYS_30"}
}

// Events starting in next 2 hours
{
  "startTime": {"between": ["$NOW+MINUTES_30", "$NOW+HOURS_2"]}
}

Complex Date Ranges

// Active within timeframe
{
  "AND": [
    {"startDate": {"lte": "$NOW+DAYS_7"}},
    {"endDate": {"gte": "$NOW-DAYS_1"}}
  ]
}

// Recent activity with future scheduling
{
  "OR": [
    {"lastActivity": {"gte": "$NOW-DAYS_7"}},
    {"nextScheduled": {"lte": "$NOW+DAYS_30"}}
  ]
}

All Supported Formats

Future (Addition):

$NOW+SECONDS_30 - 30 seconds from now
$NOW+MINUTES_15 - 15 minutes from now
$NOW+HOURS_2 - 2 hours from now
$NOW+DAYS_7 - 7 days from now
$NOW+WEEKS_3 - 3 weeks from now
$NOW+MONTHS_6 - 6 months from now
$NOW+YEARS_1 - 1 year from now

Past (Subtraction):

$NOW-SECONDS_30 - 30 seconds ago
$NOW-MINUTES_15 - 15 minutes ago
$NOW-HOURS_2 - 2 hours ago
$NOW-DAYS_7 - 7 days ago
$NOW-WEEKS_3 - 3 weeks ago
$NOW-MONTHS_6 - 6 months ago
$NOW-YEARS_1 - 1 year ago

Singular Forms:

$NOW+DAY_1 - 1 day from now
$NOW-HOUR_1 - 1 hour ago

Combining with Type Casting

// Date-only comparison with relative dates
{
  "createdAt": {"gte": "$NOW-DAYS_30", "cast": "date"}
}

// Time-only filtering with relative times
{
  "workStartTime": {"between": ["$NOW-HOURS_1", "$NOW+HOURS_8"], "cast": "time"}
}

Error Handling

Invalid patterns: Variables remain unchanged (e.g., $NOW+INVALID_5 stays as is)
Large numbers: Supported up to reasonable limits
Invalid syntax: Malformed patterns are ignored

Complete Examples

E-commerce Product Search

{
  "filter": {
    "AND": [
      {"status": {"eq": "active"}},
      {"price": {"between": [10, 100]}},
      {
        "OR": [
          {"category.slug": {"in": ["electronics", "computers"]}},
          {"tags": {"arraycontains": ["bestseller"]}}
        ]
      },
      {"inventory.stock": {"gt": 0}}
    ]
  },
  "relCondition": {
    "reviews": {
      "approved": {"eq": true},
      "rating": {"gte": 4}
    },
    "category": {
      "active": {"eq": true}
    }
  }
}

Blog Post Complex Filter with Casting and Relative Dates

{
  "filter": {
    "AND": [
      {
        "OR": [
          {"authorId": {"eq": "$CURRENT_USER.id"}},
          {"status": {"eq": "published"}}
        ]
      },
      {"publishedAt": {"between": ["$NOW-DAYS_30", "$NOW"]}},
      {"title": {"iLike": "javascript"}},
      {"tags": {"arraycontains": ["tutorial", "guide"]}},
      {"workStartTime": {"between": ["08:00:00", "18:00:00"], "cast": "time"}},
      {"viewCount": {"like": "1", "cast": "text"}}
    ]
  },
  "relCondition": {
    "author": {
      "status": {"eq": "active"},
      "lastLoginAt": {"gte": "$NOW-DAYS_7"},
      "profile": {
        "verified": {"eq": true},
        "createdAt": {"gte": "2020-01-01", "cast": "date"}
      }
    },
    "comments": {
      "approved": {"eq": true},
      "flagged": {"eq": false},
      "createdAt": {"gte": "$NOW-HOURS_24"}
    },
    "category": {
      "name": {"notIn": ["spam", "deleted"]},
      "active": {"eq": true}
    }
  }
}

Geospatial Store Locator

{
  "filter": {
    "AND": [
      {"status": {"eq": "open"}},
      {
        "location": {
          "dwithin": {
            "geometry": {
              "type": "Point",
              "coordinates": [-74.0060, 40.7128]
            },
            "distance": 5000
          }
        }
      }
    ]
  },
  "relCondition": {
    "inventory": {
      "productId": {"eq": "target-product-uuid"},
      "stock": {"gt": 0}
    },
    "hours": {
      "dayOfWeek": {"eq": "monday"},
      "isOpen": {"eq": true}
    }
  }
}

User Permission Filter

{
  "filter": {
    "tenant_Id": {"eq": "$CURRENT_TENANT"}
  },
  "relCondition": {
    "userRoles": {
      "role_Id": {"in": ["admin-role-id", "editor-role-id"]},
      "role": {
        "active": {"eq": true},
        "permissions": {"arraycontains": ["posts.read", "posts.write"]}
      }
    },
    "profile": {
      "verified": {"eq": true},
      "status": {"ne": "suspended"}
    }
  }
}

Array Field Complex Filtering

{
  "filter": {
    "AND": [
      {"skills": {"arraycontains": ["javascript", "nodejs"]}},
      {"certifications": {"arraycontained": ["aws", "docker", "kubernetes", "react"]}},
      {"languages": {"arraycontains": ["english"]}}
    ]
  },
  "relCondition": {
    "department": {
      "technologies": {"arraycontains": ["nodejs", "postgresql"]},
      "budget": {"gt": 100000}
    },
    "projects": {
      "technologies": {"arraycontains": ["react", "nodejs"]},
      "status": {"eq": "active"}
    }
  }
}

Column-to-Column Comparison Examples

Event Management System

{
  "filter": {
    "AND": [
      // Find invalid events (end time before start time)
      {"endTime": {"lt": "$COL(startTime)"}},
      
      // Events that are over capacity
      {"currentAttendees": {"gt": "$COL(maxAttendees)"}},
      
      // Events with same start and end date (single day events)
      {"startDate": {"eq": "$COL(endDate)", "cast": "date"}},
      
      {"status": {"in": ["scheduled", "active"]}}
    ]
  }
}

Project Budget Analysis

{
  "filter": {
    "OR": [
      // Projects over budget
      {"actualCost": {"gt": "$COL(estimatedCost)"}},
      
      // Projects completed under estimated hours
      {"actualHours": {"lt": "$COL(estimatedHours)"}},
      
      // Projects with timeline issues (completed before start)
      {"completedDate": {"lt": "$COL(startDate)", "cast": "date"}}
    ]
  },
  "relCondition": {
    "client": {
      "status": {"eq": "active"}
    }
  }
}

Employee Performance Review

{
  "filter": {
    "AND": [
      // Employees earning more than department minimum
      {"salary": {"gt": "$COL(department.minSalary)"}},
      
      // Employees with recent performance reviews
      {"lastReviewDate": {"gte": "$COL(hireDate)"}},
      
      // Working hours comparison with casting
      {"actualWorkHours": {
        "gte": "$COL(contractedHours::time)",
        "cast": "time"
      }}
    ]
  },
  "relCondition": {
    "department": {
      "active": {"eq": true},
      "budget": {"gt": 0}
    }
  }
}

Performance Tips

Efficient Filtering

// ✅ GOOD: Use indexed fields first
{
  "status": {"eq": "published"},     // Indexed
  "createdAt": {"gt": "2025-01-01"}, // Indexed
  "title": {"like": "specific"}      // Full-text search
}

// ❌ BAD: Complex operations on non-indexed fields
{
  "description": {"like": "long text search"},  // Not indexed
  "$customData->deep.nested.field$": {"eq": "value"}  // JSON path, slow
}

// ✅ GOOD: Limit relationship depth
{
  "author.status": {"eq": "active"}  // 1 level deep
}

// ❌ BAD: Too many relationship levels
{
  "author.profile.company.department.manager.status": {"eq": "active"}  // 5 levels deep
}

Advanced Query Guide - Advanced query patterns
Database Schema Guide - Field types and relationships
Item Routes - API endpoints

← Back to Documentation Home

On this page