Skip to main content

Filter Expressions

The filter parameter on GraphQL queries accepts a Lucene query string. The system parses the query and translates it into LINQ expressions that are executed against the database.

Basic Syntax​

filter: "fieldName:value"

Field names are case-insensitive and support dot-notation for nested properties.

Operators​

Exact Match​

filter: "status:Active"
filter: "orderId:12345"

Negation​

Prefix with NOT or - to negate:

filter: "NOT status:Active"
filter: "-status:Active"

Use * for pattern matching (translates to SQL ILIKE):

filter: "name:*express*"     # contains
filter: "name:Ship*"         # starts with
filter: "name:*Ltd"          # ends with
note

Wildcards only apply to string fields and custom values. For non-string simple types (int, bool, etc.), wildcards are automatically stripped and an exact match is performed instead.

Exists / Not Null​

Use * as the value to check that a field is not null:

filter: "deliveryDate:*"      # deliveryDate IS NOT NULL
filter: "-deliveryDate:*"     # deliveryDate IS NULL

NULL Check​

Use NULL as the value to match null fields:

filter: "deliveryDate:NULL"   # deliveryDate IS NULL
filter: "-deliveryDate:NULL"  # deliveryDate IS NOT NULL

Range Queries​

Use bracket syntax for ranges:

filter: "created:[2024-01-01 TO 2024-12-31]"    # inclusive both ends
filter: "created:{2024-01-01 TO 2024-12-31}"    # exclusive both ends
filter: "amount:[100 TO *]"                      # >= 100
filter: "amount:[* TO 500]"                      # <= 500

Boolean Logic​

Combine conditions with AND, OR, and grouping:

filter: "status:Active AND priority:High"
filter: "status:Active OR status:Pending"
filter: "(status:Active OR status:Pending) AND type:Import"

The default operator between terms is AND.

Nested Properties​

Use dot notation to filter by nested object properties:

filter: "contact.name:Acme*"
filter: "route.origin.code:LAX"

Collection Properties​

When filtering on a collection property, the system generates an Any() expression:

filter: "commodities.description:*electronics*"

This translates to: x.Commodities.Any(c => c.Description.Contains("electronics")).

Filtered Collections​

Use bracket syntax to filter within a specific subset of a collection:

filter: "charges[chargeType:Freight].amount:[100 TO *]"

This translates to:

x.Charges.Where(c => c.ChargeType == "Freight").Any(c => c.Amount >= 100)

Bracket filters work with all filter operators, including range queries:

filter: "events[type:Departure].eventDate:[2024-01-01 TO 2024-12-31]"
filter: "children[category:Import].priority:[1 TO 10]"

When multiple bracket filters target the same collection in a single query, each filter is scoped independently.

Custom Value Filtering​

Filter by custom field values stored in the customValues JSONB column:

filter: "customValues.invoiceNumber:INV*"
filter: "customValues.carrierId:123"
filter: "customValues.pickUpDate:NULL"

Custom Value NULL Behavior​

Filtering customValues.field:NULL checks three conditions:

  1. The key does not exist in the dictionary
  2. The key exists but the value is null
  3. The key exists but the value is an empty string

All three conditions are treated as "null" for filtering purposes.

Join Filter Syntax​

Filter by a property on a related entity referenced through a custom value foreign key. See Join Expressions for full details.

filter: "customValues.carrierId->contact.name:Acme*"

This builds a correlated subquery that joins to the Contacts table through the carrierId custom value.

Business Date Math​

Filter expressions support business date math tokens when an IBusinessDateMathResolver is available:

TokenDescription
BHOURBusiness hour calculation
BDAYBusiness day calculation
filter: "dueDate:[now-2BDAY TO now]"

Business date math accounts for the organization's configured business hours and holidays.

GraphQL Usage​

query {
  orders(
    organizationId: 1
    filter: "status:Active AND customValues.priority:High"
    orderBy: "-created"
    limit: 25
  ) {
    items {
      orderId
      status
      customValues
    }
    totalCount
  }
}