Filtering Syntax (FilterNode)

The FilterNode is a strict, recursive JSON object utilized across the entire Struktural platform to define dynamic database queries.

It is used in:

1. Views: The PredefinedFilter property on Grids, Calendars, and Maps.
2. Workflows: The AudienceFilter property on Scheduled cron triggers.
3. REST APIs: The Filter payload inside EntityQueryRequest objects.
4. Reports: Restricting the underlying data set before aggregations occur.

Basic Structure

A FilterNode acts as either a Rule (a leaf node that executes a comparison) or a Group (a logical container for other nodes).

Rule Node Example

{
  "Field": "Status",
  "Operator": "eq",
  "Value": "Active",
  "IsNegated": false
}

Group Node Example

{
  "Logic": "AND",
  "Children": [
    { "Field": "Category", "Operator": "eq", "Value": "Hardware" },
    { "Field": "Price", "Operator": "gt", "Value": 100 }
  ]
}

Operators

The Operator string dictates how the database provider (PostgreSQL, SQL Server) evaluates the value.

String Operators

• eq: Exact match.
• neq: Not equal.
• contains: SQL LIKE '%value%'.
• startswith: SQL LIKE 'value%'.
• endswith: SQL LIKE '%value'.
• empty: Evaluates if the string is null or empty.
• not_empty: Evaluates if the string has content.

Numeric & Boolean Operators

• eq, neq: Equals / Not Equals.
• gt: Greater than (>).
• gte: Greater than or equal to (>=).
• lt: Less than (<).
• lte: Less than or equal to (<=).
• between: Requires Value and Value2. Evaluates x >= Value AND x <= Value2.

Dynamic Date Mathematics

When operating on Date or DateTime fields, the Value property accepts powerful string expressions evaluated at runtime based on UTC time.

Allowed Date Operators: on, not_on, after, before, between, empty, not_empty.

Expression Syntax (Case-insensitive, spaces ignored):

• "now": Current UTC Timestamp.
• "today": Current UTC Date at midnight (00:00:00).
• "now + 5": Adds 5 days to the current timestamp.
• "today - 30": Subtracts 30 days from midnight today.
• "2024-12-31T15:30:00Z": Standard ISO 8601 string for fixed targeting.

Example: Find records expiring in the next 7 days:

{
  "Field": "ExpirationDate",
  "Operator": "between",
  "Value": "now",
  "Value2": "now + 7"
}

Filtering EAV Metadata Fields

Fields of type Metadata implement an Entity-Attribute-Value pattern. Standard relational querying does not work natively. To filter by a specific dynamic attribute, you MUST provide the MetadataKey and MetadataType.

{
  "Field": "CustomProperties",
  "MetadataKey": "Voltage",
  "MetadataType": "Int",
  "Operator": "gt",
  "Value": 110
}

• Field: The name of the Metadata field on the main entity.
• MetadataKey: The dynamic dictionary key (e.g., "Voltage", "Color").
• MetadataType: Dictates the underlying SQL column to query. Must be one of: String, Int, Decimal, Money, Boolean, Date, DateTime.