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
JSONB Operators
Range Operators
Null Check Operators
Geospatial Operators
Logical Operators
Type Casting
Column-to-Column Comparisons
Relational Field Filtering
RelConditions 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"]
  }
}

JSONB Operators

BAASIX provides comprehensive support for querying JSONB (JSON Binary) fields in PostgreSQL. These operators allow you to filter records based on JSON structure, key existence, nested values, and more.

Containment Operators

Operator	PostgreSQL	Description	Example
`jsonbContains`	`@>`	JSONB contains the specified value	`{"metadata": {"jsonbContains": {"status": "active"}}}`
`jsonbContainedBy`	`<@`	JSONB is contained by the specified value	`{"metadata": {"jsonbContainedBy": {"a": 1, "b": 2, "c": 3}}}`
`jsonbNotContains`	`NOT @>`	JSONB does NOT contain the specified value	`{"metadata": {"jsonbNotContains": {"deleted": true}}}`

// Find products with active status in metadata
{
  "metadata": {"jsonbContains": {"status": "active"}}
}

// Find products with specific type AND category
{
  "metadata": {"jsonbContains": {"type": "electronics", "category": "gadgets"}}
}

// Find products that are NOT marked as deleted
{
  "metadata": {"jsonbNotContains": {"deleted": true}}
}

// Find products whose metadata is a subset of the given object
{
  "metadata": {"jsonbContainedBy": {"status": "active", "type": "electronics", "premium": true}}
}

Key Existence Operators

Operator	PostgreSQL	Description	Example
`jsonbHasKey`	`?`	Check if a top-level key exists	`{"metadata": {"jsonbHasKey": "discount"}}`
`jsonbHasAnyKeys`	`?	`	Check if any of the keys exist
`jsonbHasAllKeys`	`?&`	Check if all of the keys exist	`{"metadata": {"jsonbHasAllKeys": ["status", "type"]}}`

// Find products that have a discount field
{
  "metadata": {"jsonbHasKey": "discount"}
}

// Find products with either discount OR promo code
{
  "metadata": {"jsonbHasAnyKeys": ["discount", "promoCode", "salePrice"]}
}

// Find products that have ALL required fields
{
  "metadata": {"jsonbHasAllKeys": ["status", "type", "price", "stock"]}
}

Key Value Comparison Operators

These operators extract a specific key's value and compare it. They support strings, numbers, booleans, and null values.

String Comparisons

Operator	Description	Example
`jsonbKeyEquals`	Key value equals	`{"metadata": {"jsonbKeyEquals": {"key": "status", "value": "active"}}}`
`jsonbKeyNotEquals`	Key value not equals	`{"metadata": {"jsonbKeyNotEquals": {"key": "status", "value": "deleted"}}}`
`jsonbKeyIn`	Key value in list	`{"metadata": {"jsonbKeyIn": {"key": "status", "values": ["active", "pending"]}}}`
`jsonbKeyNotIn`	Key value not in list	`{"metadata": {"jsonbKeyNotIn": {"key": "status", "values": ["deleted", "archived"]}}}`
`jsonbKeyLike`	Pattern matching (ILIKE)	`{"metadata": {"jsonbKeyLike": {"key": "name", "pattern": "%john%"}}}`

// Find products with specific status
{
  "metadata": {"jsonbKeyEquals": {"key": "status", "value": "active"}}
}

// Find products NOT in certain statuses
{
  "metadata": {"jsonbKeyNotEquals": {"key": "status", "value": "deleted"}}
}

// Find products with status in a list
{
  "metadata": {"jsonbKeyIn": {"key": "category", "values": ["electronics", "computers", "gadgets"]}}
}

// Pattern matching on JSONB key value
{
  "metadata": {"jsonbKeyLike": {"key": "description", "pattern": "%wireless%"}}
}

Numeric Comparisons

Operator	Description	Example
`jsonbKeyGt`	Key value greater than	`{"metadata": {"jsonbKeyGt": {"key": "price", "value": 100}}}`
`jsonbKeyGte`	Key value greater than or equal	`{"metadata": {"jsonbKeyGte": {"key": "price", "value": 100}}}`
`jsonbKeyLt`	Key value less than	`{"metadata": {"jsonbKeyLt": {"key": "stock", "value": 10}}}`
`jsonbKeyLte`	Key value less than or equal	`{"metadata": {"jsonbKeyLte": {"key": "stock", "value": 10}}}`

// Find products with price greater than 100
{
  "metadata": {"jsonbKeyGt": {"key": "price", "value": 100}}
}

// Find products with price in range (using multiple operators)
{
  "AND": [
    {"metadata": {"jsonbKeyGte": {"key": "price", "value": 50}}},
    {"metadata": {"jsonbKeyLte": {"key": "price", "value": 200}}}
  ]
}

// Find low stock products
{
  "metadata": {"jsonbKeyLt": {"key": "stock", "value": 10}}
}

// Find products with at least 100 in stock
{
  "metadata": {"jsonbKeyGte": {"key": "stock", "value": 100}}
}

Boolean Comparisons

The jsonbKeyEquals operator automatically handles boolean values:

// Find featured products (boolean true)
{
  "settings": {"jsonbKeyEquals": {"key": "featured", "value": true}}
}

// Find non-featured products (boolean false)
{
  "settings": {"jsonbKeyEquals": {"key": "featured", "value": false}}
}

// Find premium products
{
  "settings": {"jsonbKeyEquals": {"key": "premium", "value": true}}
}

Null Value Checks

Operator	Description	Example
`jsonbKeyIsNull`	Key value is null or missing	`{"metadata": {"jsonbKeyIsNull": "deletedAt"}}`
`jsonbKeyIsNotNull`	Key value is not null	`{"metadata": {"jsonbKeyIsNotNull": "publishedAt"}}`
`jsonbKeyEquals` with null	Key value equals null	`{"metadata": {"jsonbKeyEquals": {"key": "deletedAt", "value": null}}}`

// Find products where deletedAt is null (not deleted)
{
  "metadata": {"jsonbKeyIsNull": "deletedAt"}
}

// Find products that have been published (publishedAt is set)
{
  "metadata": {"jsonbKeyIsNotNull": "publishedAt"}
}

// Alternative: using jsonbKeyEquals with null
{
  "metadata": {"jsonbKeyEquals": {"key": "deletedAt", "value": null}}
}

JSON Path Operators

Operator	PostgreSQL	Description	Example
`jsonbPathExists`	`@?`	Check if JSON path returns any items	`{"profile": {"jsonbPathExists": "$.user.preferences"}}`
`jsonbPathMatch`	`@@`	Check if JSON path predicate is true	`{"metadata": {"jsonbPathMatch": "$.price > 100"}}`

// Check if a nested path exists
{
  "profile": {"jsonbPathExists": "$.user.preferences.notifications"}
}

// Check if path with array exists
{
  "metadata": {"jsonbPathExists": "$.tags[*]"}
}

// JSON path predicate (price > 100)
{
  "metadata": {"jsonbPathMatch": "$.price > 100"}
}

Deep Nested Value Access

The jsonbDeepValue operator allows you to access deeply nested values using a path array:

Operator	PostgreSQL	Description
`jsonbDeepValue`	`#>>`	Access nested value by path array

Supported operations: eq, ne, gt, gte, lt, lte, like, ilike

// Access nested value with equality check
{
  "profile": {
    "jsonbDeepValue": {
      "path": ["user", "preferences", "theme"],
      "value": "dark"
    }
  }
}

// Access nested value with comparison
{
  "profile": {
    "jsonbDeepValue": {
      "path": ["user", "stats", "loginCount"],
      "value": 10,
      "op": "gte"
    }
  }
}

// Pattern matching on nested value
{
  "profile": {
    "jsonbDeepValue": {
      "path": ["user", "name"],
      "value": "%john%",
      "op": "ilike"
    }
  }
}

// Check if nested value is null
{
  "profile": {
    "jsonbDeepValue": {
      "path": ["user", "deletedAt"],
      "value": null
    }
  }
}

// Numeric comparison on deeply nested field
{
  "profile": {
    "jsonbDeepValue": {
      "path": ["settings", "limits", "maxItems"],
      "value": 100,
      "op": "lt"
    }
  }
}

JSONB Array Operations

Operator	Description	Example
`jsonbArrayLength`	Check JSONB array length	`{"tags": {"jsonbArrayLength": {"op": "gte", "value": 3}}}`
`jsonbTypeOf`	Check JSONB value type	`{"metadata": {"jsonbTypeOf": {"type": "object"}}}`

Supported operations for jsonbArrayLength: eq, ne, gt, gte, lt, lte

Supported types for jsonbTypeOf: object, array, string, number, boolean, null

// Find records with at least 3 tags
{
  "tags": {"jsonbArrayLength": {"op": "gte", "value": 3}}
}

// Find records with exactly 5 items
{
  "items": {"jsonbArrayLength": {"op": "eq", "value": 5}}
}

// Check array length at a specific path
{
  "metadata": {"jsonbArrayLength": {"path": "categories", "op": "gt", "value": 0}}
}

// Find records where metadata is an object
{
  "metadata": {"jsonbTypeOf": {"type": "object"}}
}

// Find records where tags is an array
{
  "tags": {"jsonbTypeOf": {"type": "array"}}
}

// Check type at a specific path
{
  "metadata": {"jsonbTypeOf": {"path": "price", "type": "number"}}
}

Combining JSONB Operators

JSONB operators can be combined with other operators and logical operators:

// Combine JSONB with regular field filters
{
  "AND": [
    {"metadata": {"jsonbContains": {"status": "active"}}},
    {"name": {"startsWith": "Product"}},
    {"createdAt": {"gte": "2025-01-01"}}
  ]
}

// Multiple JSONB conditions
{
  "AND": [
    {"metadata": {"jsonbContains": {"type": "electronics"}}},
    {"metadata": {"jsonbKeyGt": {"key": "price", "value": 100}}},
    {"metadata": {"jsonbKeyLte": {"key": "price", "value": 500}}},
    {"settings": {"jsonbKeyEquals": {"key": "featured", "value": true}}}
  ]
}

// OR conditions with JSONB
{
  "OR": [
    {"metadata": {"jsonbContains": {"premium": true}}},
    {"metadata": {"jsonbKeyGt": {"key": "sales", "value": 1000}}}
  ]
}

// Complex nested conditions
{
  "AND": [
    {
      "OR": [
        {"metadata": {"jsonbContains": {"category": "electronics"}}},
        {"metadata": {"jsonbContains": {"category": "computers"}}}
      ]
    },
    {"metadata": {"jsonbKeyGte": {"key": "rating", "value": 4}}},
    {"metadata": {"jsonbKeyIsNotNull": "stock"}}
  ]
}

JSONB Operators Quick Reference

Operator	PostgreSQL	Use Case
`jsonbContains`	`@>`	Check if JSONB contains key-value pairs
`jsonbContainedBy`	`<@`	Check if JSONB is subset of value
`jsonbNotContains`	`NOT @>`	Check JSONB doesn't contain value
`jsonbHasKey`	`?`	Check if key exists
`jsonbHasAnyKeys`	`?	`
`jsonbHasAllKeys`	`?&`	Check if all keys exist
`jsonbKeyEquals`	`->>` + `=`	Compare key's value (string/number/boolean/null)
`jsonbKeyNotEquals`	`->>` + `!=`	Key value not equals
`jsonbKeyGt`	`->>` + `>`	Key value greater than (numeric)
`jsonbKeyGte`	`->>` + `>=`	Key value greater than or equal (numeric)
`jsonbKeyLt`	`->>` + `<`	Key value less than (numeric)
`jsonbKeyLte`	`->>` + `<=`	Key value less than or equal (numeric)
`jsonbKeyIn`	`->>` + `IN`	Key value in list
`jsonbKeyNotIn`	`->>` + `NOT IN`	Key value not in list
`jsonbKeyLike`	`->>` + `ILIKE`	Pattern match on key value
`jsonbKeyIsNull`	`->>` + `IS NULL`	Key value is null
`jsonbKeyIsNotNull`	`->>` + `IS NOT NULL`	Key value is not null
`jsonbPathExists`	`@?`	JSON path exists
`jsonbPathMatch`	`@@`	JSON path predicate matches
`jsonbArrayLength`	`jsonb_array_length()`	Check array length
`jsonbTypeOf`	`jsonb_typeof()`	Check value type
`jsonbDeepValue`	`#>>`	Access deeply nested values

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"}
}

RelConditions System

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

RelConditions Syntax

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

Deep RelConditions Nesting

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

RelConditions with Logical Operators

{
  "relConditions": {
    "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

The relConditions parameter (always plural) is a deep filter for filtering within array relations (O2M and M2M). It filters what items appear in the returned arrays. Dynamic variables work within relConditions:

// Filter items within relation arrays using dynamic variables
{
  "relConditions": {
    "comments": {
      "authorId": {"eq": "$CURRENT_USER.id"}
    },
    "tasks": {
      "assignedTo": {"eq": "$CURRENT_USER.id"},
      "status": {"eq": "active"}
    }
  }
}

Note: relConditions filters what appears in the array for O2M (One-to-Many) and M2M (Many-to-Many) relations. Regular filter can also be used on these relations but filters the main records instead.

See Item Query Reference Guide - relConditions for comprehensive documentation.

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}}
    ]
  },
  "relConditions": {
    "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"}}
    ]
  },
  "relConditions": {
    "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
          }
        }
      }
    ]
  },
  "relConditions": {
    "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"}
  },
  "relConditions": {
    "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"]}}
    ]
  },
  "relConditions": {
    "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"}}
    ]
  },
  "relConditions": {
    "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"
      }}
    ]
  },
  "relConditions": {
    "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