Where Clause Documentation

Powerful Filter System with human-readable syntax for complex queries

Overview

Our Filter System provides a powerful, human-readable query language for filtering entities and their relations. It works seamlessly across the entire platform - in the application UI, the query system, REST APIs, and correctly handles changesets for time-based queries.

Universal Usage

Same filter syntax works in UI filters, query system, and REST API endpoints

AI-Assisted Design

Use AI Chat to help design complex filters or double-click filter text to edit syntax

Changeset Compatible

Works with historical data and point-in-time queries through changeset integration

Filter Examples

Simple Equality

Filter by exact field value

name = "John"

[badge:Basic|secondary|right]

Double Equals Conversion

Double equals automatically converts to single equals

name == "John"

[badge:Basic|secondary|right]

Not Equals with Aliases

Multiple ways to express not equals

name != "John" or status <> "inactive" or type ne "old"

[badge:Basic|secondary|right]

Numeric Comparisons

Greater than with decimal numbers

age > 30.5 and price gte 100

[badge:Numeric|secondary|right]

Boolean Checks

Boolean field comparisons

visible is true and published is not false

[badge:Boolean|secondary|right]

Negative Numbers

Support for negative numeric values

balance = -1 and adjustment < -50

[badge:Numeric|secondary|right]

Null and Empty Checks

Various null and empty validations

deleted_at is null and name is not empty

[badge:Null|secondary|right]

Empty vs Null Distinction

Understanding empty (null or “”) vs null

description is empty or notes is not null

[badge:Null|secondary|right]

Pattern Matching

Various text pattern operations

name like "test" and email ends "@gmail.com"

[badge:Pattern|secondary|right]

Case-Insensitive Patterns

Case-insensitive text matching

name ilike "john" or title begins similar "TEST"

[badge:Pattern|secondary|right]

Negated Pattern Matching

Negative pattern operations

name not like "temp" and email not ends ".test"

[badge:Pattern|secondary|right]

Set Membership

Check if value is in a list

status in ("active", "pending", "approved")

[badge:Set|secondary|right]

Numeric Set Operations

Set operations with numbers

id in (1, 2, 3) and priority not in (0, 10)

[badge:Set|secondary|right]

Range Queries

Between and range operations

age between 18 and 65

[badge:Range|secondary|right]

Negated Range

Not between operations

price not between 100 and 500

[badge:Range|secondary|right]

Complex Logical Conditions

Parentheses and complex logic

(name = "John" or name = "Jane") and age > 30

[badge:Complex|secondary|right]

Negation with Parentheses

NOT operator with grouping

not (status = "inactive" or deleted is true)

[badge:Complex|secondary|right]

Simple Relation Filtering (MANY)

Filter by related entity fields - uses EXISTS

products.name = "Electronics"

[badge:Relations|secondary|right]

Relation with Parameters

Using parameters in relation queries

products.price > $minPrice

[badge:Relations|secondary|right]

Relation with Brackets

Alternative bracket syntax for relations

products[name = "Electronics" and price > 100]

[badge:Relations|secondary|right]

Multiple Relation Conditions

Multiple conditions on the same relation

products.category_id = 1 and products.name like "test"

[badge:Relations|secondary|right]

Nested Relations

Deep relation traversal

products.category.name = "Electronics"

[badge:Relations|secondary|right]

Complex Nested Relations with Brackets

Combining brackets with nested relations

products[name like "test"].category.name = "Electronics"

[badge:Relations|secondary|right]

Double Bracket Relations

Multiple bracket conditions in chain

products[name like "test"].category[name = "Electronics"]

[badge:Relations|secondary|right]

Date Functions

Built-in date and time functions

dueDate > now() and createdAt > currentYear()

[badge:Functions|secondary|right]

User Context Function

Current user reference function

user_id = me() and assigned_to != me()

[badge:Functions|secondary|right]

String Functions

Text transformation and length functions

lower(name) = "john" and length(description) > 10

[badge:Functions|secondary|right]

Date/Time Extraction Functions

Extract parts from date/time fields

year(created_at) = 2023 and month(birth_date) = 12

[badge:Functions|secondary|right]

Quarter Functions

Extract quarter from date fields

quarter(order_date) = 4 and yearquarter(fiscal_date) = "2023-Q4"

[badge:Functions|secondary|right]

Advanced Date Functions

Week numbers and formatted date strings

dayofweek(event_date) = 1 and yearmonth(created_at) = "2023-12" and yearmonthday(created_at) = "2023-12-25"

[badge:Functions|secondary|right]

Null Checking Function

Check for null values using isnull function

isnull(optional_field) = true and isnull(name) = false

[badge:Functions|secondary|right]

Date Range Functions (Year/Month)

Get start and end boundaries for time periods

created_at >= startOfYear(now()) and created_at <= endOfMonth(now())

[badge:Functions|secondary|right]

Date Range Functions (Day/Hour)

Precise time boundaries for detailed analysis

event_time >= startOfDay("2025-01-01") and event_time <= endOfHour(now())

[badge:Functions|secondary|right]

Complex Date Range Filtering

Combining multiple date range functions

startOfYear(created_at) = startOfYear(now()) and endOfMonth(updated_at) >= startOfMonth(now())

[badge:Functions|secondary|right]

Time Extraction - Hour/Minute/Second

Extract hour, minute, or second from datetime fields

hour(timestamp) = 14 and minute(timestamp) = 30 and second(timestamp) = 45

[badge:Time Functions|secondary|right]

Business Hours Filtering

Filter records within working hours

hour(created_at) between 9 and 17

[badge:Time Functions|secondary|right]

Time-Based Grouping

Combine time extraction with other conditions

hour(orderDate) = 14 and dayofweek(orderDate) = 1

[badge:Time Functions|secondary|right]

Math Functions - Round/Floor/Ceiling

Apply mathematical rounding operations

round(price) = 100 and floor(rating) = 4 and ceiling(score) = 10

[badge:Math Functions|secondary|right]

Price Rounding

Round prices for comparisons

round(price) between 90 and 110

[badge:Math Functions|secondary|right]

Combined Math Operations

Nest math functions with other operations

round(price * 1.21) = 121

[badge:Math Functions|secondary|right]

String Concatenation

Combine multiple strings with concat function

concat(firstName, lastName) = "JohnDoe"

[badge:String Functions|secondary|right]

Substring Extraction

Extract part of a string (0-based indexing)

substring(code, 0, 3) = "PRD"

[badge:String Functions|secondary|right]

Find position of substring (returns -1 if not found)

indexof(email, "@") > 0

[badge:String Functions|secondary|right]

Trim Whitespace

Remove leading/trailing spaces from strings

trim(name) = "Product"

[badge:String Functions|secondary|right]

Combined String Operations

Nest multiple string functions together

concat(trim(firstName), " ", trim(lastName)) = "John Doe"

[badge:String Functions|secondary|right]

Advanced String Manipulation

Extract substring using dynamic position

substring(email, indexof(email, "@"), 5) = "@acme"

[badge:String Functions|secondary|right]

Basic Arithmetic - Addition

Add values to fields

price + 10 = 110

[badge:Arithmetic|secondary|right]

Basic Arithmetic - Subtraction

Subtract values from fields

quantity - 5 > 10

[badge:Arithmetic|secondary|right]

Basic Arithmetic - Multiplication

Multiply field values

price * 2 = 200

[badge:Arithmetic|secondary|right]

Basic Arithmetic - Division

Divide field values

total / count = 25

[badge:Arithmetic|secondary|right]

Basic Arithmetic - Modulo

Check remainder after division (useful for even/odd checks)

id % 2 = 0

[badge:Arithmetic|secondary|right]

Price with VAT Calculation

Calculate VAT on prices

price * 1.21 = priceWithVat

[badge:Arithmetic|secondary|right]

Discount Calculation

Apply percentage discount

price - (price * 0.1) < 100

[badge:Arithmetic|secondary|right]

Operator Precedence

Multiplication before addition (standard math rules)

price + 10 * 2 = 50

[badge:Arithmetic|secondary|right]

Complex Arithmetic Expression

Multiple operators in one expression

(price + shipping) * 1.21 > 100

[badge:Arithmetic|secondary|right]

Percentage Calculation

Calculate completion percentage

completed / total * 100 > 80

[badge:Arithmetic|secondary|right]

Arithmetic with Math Functions

Combine arithmetic operators with math functions

round(price) * 2 = 200 and floor(quantity / 10) = 5

[badge:Combined|secondary|right]

Time-Based Arithmetic

Use arithmetic with time extraction functions

hour(timestamp) * 2 = 20 or minute(timestamp) + 30 = 60

[badge:Combined|secondary|right]

String and Math Combined

Mix string manipulation with math operations

length(trim(name)) > 5 and round(price) < 100

[badge:Combined|secondary|right]

Complex Nested Functions

Deeply nested function calls

round(hour(timestamp)) = 14 and substring(code, indexof(code, "-"), 4) = "-PRD"

[badge:Combined|secondary|right]

Field Aliases

Using field aliases in conditions

published_at >= "2021-01-01" and publishedAt <= "2021-12-31"

[badge:Aliases|secondary|right]

Translatable Fields - Default Language

Query translatable fields in user’s default/selected language

name = "Hello" and description like "Product"

[badge:Translatable|secondary|right]

Translatable Fields - Language-Specific

Query specific language variants using language code suffix

name:cs = "Ahoj" and name:en = "Hello" and description:de like "Produkt"

[badge:Translatable|secondary|right]

Quote Handling

Various quote types and escaping

name = "John" and description = 'Single quotes' and title = "Escaped \"quotes\""

[badge:Quotes|secondary|right]

Basic Aggregation - Count

Count related records

count(products) > 10

[badge:Aggregation|secondary|right]

Filtered Aggregation - Count

Count related records matching a condition

count(products[visible = true]) > 5

[badge:Aggregation|secondary|right]

Sum Aggregation

Sum numeric field across related records

sum(products.price) > 1000

[badge:Aggregation|secondary|right]

Average Aggregation

Calculate average of numeric field

avg(products.rating) > 4.5

[badge:Aggregation|secondary|right]

Min/Max Aggregation

Find minimum or maximum values

min(products.price) > 10 and max(products.stock) < 100

[badge:Aggregation|secondary|right]

Nested Relation Aggregation

Aggregate with nested relation filters

count(products[category.name = "Electronics"]) > 5

[badge:Aggregation|secondary|right]

Complex Aggregation Filters

Multiple conditions in filtered aggregations

sum(products[active = true and price > 50].price) > 1000

[badge:Aggregation|secondary|right]

Combined Aggregations

Multiple aggregation functions in one filter

count(products[visible = true]) > 10 and avg(products.rating) > 4.0

[badge:Aggregation|secondary|right]

Subtree Filtering - Direct (recursive default)

Filter products in a category subtree by code (includes all descendants)

in subtree "code:ELECTRONICS"

[badge:Hierarchical|secondary|right]

Subtree Filtering - Explicit Recursive

Filter using explicit recursive keyword

in subtree "code:ELECTRONICS" recursive

[badge:Hierarchical|secondary|right]

Subtree Filtering - Nonrecursive

Filter using nonrecursive keyword (direct children only)

in subtree 7 nonrecursive

[badge:Hierarchical|secondary|right]

Subtree Filtering - With ID

Filter using direct hierarchical ID

in subtree "42"

[badge:Hierarchical|secondary|right]

Subtree Filtering - In Relation

Filter users by products in category subtree

products[in subtree "code:FASHION"]

[badge:Hierarchical|secondary|right]

Subtree Filtering - In Relation (nonrecursive)

Filter users by products in category (direct only)

products[in subtree "code:FASHION" nonrecursive]

[badge:Hierarchical|secondary|right]

Subtree with Aggregation - Count

Count products in hierarchical category

count(products[in subtree "code:ELECTRONICS"]) > 5

[badge:Hierarchical|secondary|right]

Subtree with Aggregation - Count (nonrecursive)

Count products in category (direct only)

count(products[in subtree "code:ELECTRONICS" nonrecursive]) > 5

[badge:Hierarchical|secondary|right]

Subtree with Aggregation - Sum

Sum prices of products in luxury category tree

sum(products[in subtree "code:LUXURY"].price) > 10000

[badge:Hierarchical|secondary|right]

Subtree with Multiple Conditions

Combine subtree with other filters

in subtree "code:ELECTRONICS" and price > 100

[badge:Hierarchical|secondary|right]

Subtree in Complex Query

Count highly-rated products in category subtree

count(products[in subtree "code:BOOKS" and rating > 4]) > 3

[badge:Hierarchical|secondary|right]

Using Filters in the Application

The same filter syntax described here works throughout the entire platform, providing a consistent experience across different interfaces.

Application UI

  • Double-click any filter text to edit the raw syntax
  • Use AI Chat to help design complex filters
  • Visual filter builder converts to this syntax

API Integration

  • Query System: See Query Documentation
  • REST API: Use as filter parameter
  • Changesets: Works with historical data

Operators Reference

Complete table of supported operators with aliases and examples

OperatorAliasesDescriptionExampleCategory
===, eq, ~eq~Equalname = “John”Comparison
!=<>, ne, ~ne~Not equalname != “John”Comparison
>gt, ~gt~Greater thanage > 30Comparison
>=gte, ~gte~Greater than or equalage >= 18Comparison
<lt, ~lt~Less thanage < 65Comparison
<=lte, ~lte~Less than or equalage <= 65Comparison
is null-Field is nullname is nullNull
is not null-Field is not nullname is not nullNull
is empty-Field is null or empty stringname is emptyNull
is not empty-Field is not null and not emptyname is not emptyNull
is true-Field is truevisible is trueBoolean
is false-Field is falsevisible is falseBoolean
in-Value in setid in (1, 2, 3)Set
not in-Value not in setname not in (“active”, “pending”)Set
between … and …-Value in rangeage between 18 and 65Range
not between … and …-Value not in rangeage not between 18 and 65Range
like-Case-insensitive contains substringname like “test”Pattern
not like-Case-insensitive does not contain substringname not like “test”Pattern
ilike-Case-insensitive likename ilike “test”Pattern
like similar-Case-insensitive likename like similar “test”Pattern
begins-Starts withname begins “John”Pattern
ends-Ends withname ends “Doe”Pattern
not begins-Does not start withname not begins “John”Pattern
not ends-Does not end withname not ends “Doe”Pattern
begins similar-Case-insensitive starts withname begins similar “test”Pattern
ends similar-Case-insensitive ends withname ends similar “test”Pattern
in subtree-Match records in hierarchical subtreein subtree “code:ELECTRONICS”Hierarchical
missing ‘XX’ translation-Check if specific language translation is missingmissing “en” translationTranslation
missing any translation-Check if any configured language translation is missingmissing any translationTranslation

Logical Operators

and

Both conditions must be true

name = "John" and age > 18

[badge:Logic|outline|right]

or

At least one condition must be true

status = "active" or status = "pending"

[badge:Logic|outline|right]

not

Negates the condition

not (status = "inactive")

[badge:Logic|outline|right]

Arithmetic Operators

Perform mathematical operations on numeric fields and values

Operator Precedence

Arithmetic operators follow standard mathematical precedence rules:

  • High precedence (1): Multiplication (*), Division (/), Modulo (%)
  • Low precedence (2): Addition (+), Subtraction (-)
  • Operators of the same precedence are evaluated left-to-right
price + 10 * 2 = 50  /* Same as: price + (10 * 2) */
OperatorDescriptionPrecedenceExample
+AdditionLow (2)price + 10 = 110
-SubtractionLow (2)quantity - 5 > 10
*MultiplicationHigh (1)price * 2 = 200
/DivisionHigh (1)total / count = 25
%Modulo (remainder)High (1)id % 2 = 0

Real-World Use Cases

Price Calculations

price * 1.21 = priceWithVat

Calculate VAT (21%)

Inventory Checks

quantity % 2 = 0

Check if quantity is even

Discounts

price - (price * 0.1) < 100

10% discount under 100

Percentages

completed / total * 100 > 80

More than 80% complete

Functions Reference

Built-in functions for data transformation and extraction

FunctionDescriptionExampleCategory
hour(field)Extract hour from datetime (0-23)hour(timestamp) = 14Time Extraction
minute(field)Extract minute from datetime (0-59)minute(timestamp) = 30Time Extraction
second(field)Extract second from datetime (0-59)second(timestamp) = 45Time Extraction
round(value)Round to nearest integerround(price) = 100Math
floor(value)Round down to integerfloor(rating) = 4Math
ceiling(value)Round up to integerceiling(price) = 100Math
concat(str1, str2)Concatenate two stringsconcat(firstName, lastName) = “JohnDoe”String
indexof(str, search)Find position of substring (0-based, returns -1 if not found)indexof(email, “@”) > 0String
substring(str, start, length)Extract substring (0-based indexing)substring(code, 0, 3) = “PRD”String
trim(str)Remove leading/trailing whitespacetrim(name) = “Product”String
lower(field)Convert to lowercaselower(name) = “john”String
upper(field)Convert to uppercaseupper(name) = “JOHN”String
length(field)Get string lengthlength(name) > 5String
year(field)Extract year from dateyear(created_at) = 2023Date/Time
month(field)Extract month from date (1-12)month(created_at) = 12Date/Time
day(field)Extract day from date (1-31)day(created_at) = 25Date/Time
quarter(field)Extract quarter from date (1-4)quarter(created_at) = 4Date/Time
week(field)Extract week number from dateweek(created_at) = 52Date/Time
dayofweek(field)Extract day of week (0-6, 0=Sunday)dayofweek(created_at) = 1Date/Time
now()Current timestampcreated_at > now()Special
me()Current user IDuser_id = me()Special
isnull(field)Check if field is nullisnull(name) = trueSpecial
count(relation)Count related records (MANY relations only)count(products) > 10Aggregation
count(relation[filter])Count related records matching a filtercount(products[visible = true]) > 5Aggregation
sum(relation.field)Sum numeric field across related recordssum(products.price) > 1000Aggregation
avg(relation.field)Average numeric field across related recordsavg(products.rating) > 4.5Aggregation
min(relation.field)Minimum value of numeric field across related recordsmin(products.price) > 10Aggregation
max(relation.field)Maximum value of numeric field across related recordsmax(products.stock) < 100Aggregation

Function Nesting and Combinations

Nested String Functions

concat(trim(firstName), " ", trim(lastName)) = "John Doe"

Combine trimmed names

Math with Arithmetic

round(price * 1.21) = 121

Round VAT calculation

Dynamic String Extraction

substring(email, indexof(email, "@"), 5) = "@acme"

Extract domain from email

Time-Based Arithmetic

hour(timestamp) * 2 = 20

Mathematical operations on time

Important Notes

  • indexof(): Returns -1 if substring not found (0-based indexing)
  • substring(): Uses 0-based indexing, third parameter is length (not end position)
  • Time functions: Extract integer values (0-23 for hours, 0-59 for minutes/seconds)
  • Math functions: Round, floor, and ceiling all return integers
  • All functions are case-insensitive (HOUR, hour, Hour all work)

Aggregation Functions

Aggregate data across MANY relations for powerful analytics and filtering

Overview

Aggregation functions allow you to perform calculations across related records in MANY relations (one-to-many, many-to-many). They work with filtered subsets of data and support nested relation filters.

  • count(): Count matching records
  • sum(): Sum numeric field values
  • avg(): Calculate average of numeric fields
  • min(): Find minimum value
  • max(): Find maximum value

Supported Aggregation Functions

FunctionSyntaxDescriptionExample
countcount(relation)Count all matching recordscount(products) > 10
sumsum(relation.field)Sum numeric field valuessum(products.price) > 1000
avgavg(relation.field)Calculate average valueavg(products.rating) > 4.5
minmin(relation.field)Find minimum valuemin(products.price) > 10
maxmax(relation.field)Find maximum valuemax(products.stock) < 100

Filtered Aggregations

Use bracket notation to filter the records before aggregation:

Count with Filter

count(products[visible = true]) > 5

Count only visible products

Sum with Filter

sum(products[active = true].price) > 1000

Sum prices of active products only

Average with Filter

avg(products[price > 100].rating) > 4.0

Average rating of expensive products

Multiple Conditions

count(products[active = true and price > 50]) > 10

Complex filter conditions

Nested Relation Filters

Aggregations support filtering by nested relations (e.g., products filtered by their category):

Filter by Nested Relation

count(products[category.name = 'Electronics']) > 5

Count products in Electronics category

Sum with Nested Filter

sum(products[category.active = true].price) > 1000

Sum prices where category is active

Complex Nested Conditions

avg(products[category.name = 'Electronics' and visible = true].rating) > 4.5

Combine relation and field filters

Real-World Use Cases

E-commerce Analytics

sum(orders[status = 'completed'].total) > 10000

Find customers with over $10,000 in completed orders

Inventory Management

min(products[active = true].stock) < 10

Alert when any active product is low on stock

Quality Control

avg(reviews[verified = true].rating) < 3.0

Flag products with poor verified reviews

User Engagement

count(posts[year(created_at) = 2025]) > 50

Find highly active users in 2025

Important Notes

  • MANY relations only: Aggregations work only with one-to-many or many-to-many relations
  • Permission filters: Automatically applied to all aggregation subqueries
  • Null handling: Null values are excluded from calculations (database behavior)
  • count() variants: count(relation) counts all records; count(relation.field) counts non-null values
  • Performance: Aggregations use optimized SQL subqueries with EXISTS clauses
  • Nested filters: Use dot notation for nested relation filters (e.g., category.name)

Best Practices

Use Case-Insensitive Operators

Type: readability

Both like and ilike operators are case-insensitive for better usability

Use: name like "john" or name ilike "john"
Both work the same way (case-insensitive)

Leverage Lookup Values

Type: readability

Use lookup values for cleaner, more readable queries

Use: category = "code:ELEC"
Instead of: category.code = "ELEC"

Understand Relation Types

Type: correctness

Know the difference between ONE and MANY relations

ONE: category.name = "Electronics"
MANY: products.price > 100 (uses EXISTS)

Use Parentheses for Complex Logic

Type: readability

Use parentheses to make complex conditions clear

Use: (age < 18 or age > 65) and visible is true
For clear precedence

Choose the Right Pattern Operator

Type: performance

Use appropriate pattern matching operators

Use: name begins "John" (faster)
Instead of: name like "John%"

Use Parameters for Dynamic Queries

Type: correctness

Use parameters instead of string concatenation

Use: age > $minAge
Instead of: concatenating values directly

Leverage Field Functions

Type: readability

Use built-in functions for data transformation and validation

Use: lower(name) = "john" and year(created_at) = 2023
For case-insensitive and date filtering

Combine Functions for Complex Logic

Type: readability

Nest functions and use arithmetic for powerful transformations

Use: round(price * 1.21) > 100 and hour(created_at) between 9 and 17
For VAT calculation and business hours filtering

Use Appropriate String Functions

Type: performance

Choose the right string function for your use case

Use: indexof(email, "@") > 0 (faster)
Instead of: email like "%@%"
For checking email validity

Understand Operator Precedence

Type: correctness

Remember that * / % have higher precedence than + -

price + 10 * 2 evaluates as price + (10 * 2)
Not as (price + 10) * 2

Use Aggregations for Analytics

Type: readability

Leverage aggregation functions for powerful data analysis

Use: count(products[visible = true]) > 10
For counting filtered related records
Or: sum(orders[status = "completed"].total) > 5000
For analytics on related data

Advanced Features

Field Types & Values

Support for various data types and special values

  • Strings: 'John', "Doe"
  • Numbers: 42, 3.14, -1
  • Booleans: true, false
  • Context Functions: now(), currentYear(), me()
  • String Functions: lower(), upper(), length(), trim(), isnull()
  • Advanced String: concat(), indexof(), substring()
  • Date Functions: year(), month(), day(), week(), yearmonthday()
  • Time Extraction: hour(), minute(), second()
  • Math Functions: round(), floor(), ceiling()
  • Arithmetic: +, -, *, /, %
  • Aggregation: count(), sum(), avg(), min(), max()
  • Date Range Functions: startOfYear(), endOfYear(), startOfMonth(), endOfMonth(), startOfDay(), endOfDay(), startOfHour(), endOfHour()

Aggregation Functions

Perform calculations across MANY relations

Basic Aggregation:

count(products) > 10

Count all related records

Filtered Aggregation:

sum(products[active = true].price) > 1000

Aggregate with filter conditions

Nested Relation Filter:

count(products[category.name = 'Electronics']) > 5

Filter by nested relation fields

  • Functions: count, sum, avg, min, max
  • MANY relations only: Works with one-to-many and many-to-many
  • Permission aware: Respects entity security rules

Lookup Values

Reference entities by code, unique identifier, or external ID

Standard Lookup:

category = 'code:ELEC'

Equivalent to: category.code = ‘ELEC’

External ID Lookup:

category = 'ext:SYSTEM:CATEGORY:123'
product = 'ext:SYSTEM:NS:PROD:ABC-456'

Reference by external system ID (from ABRA Flexi, etc.)

  • Standard: code:, ean:, guid:
  • External: ext:EXTERNAL_ID (supports colons in value)
  • Storage: External IDs stored in extIds table

Relation Types

Different behavior for ONE vs MANY relations

  • ONE: category.name = 'Electronics'
  • MANY: products.price > 100

Translatable Fields

Query translatable fields in different languages

Default Language:

name = 'Hello'

Uses user’s default/selected language

Language-Specific:

name:cs = 'Ahoj'
name:en = 'Hello' and description:de like 'Produkt'

Query specific language using field:langCode syntax

  • Syntax: fieldName:languageCode = 'value'
  • Supported: cs, en, de, fr, hu, pl, sk
  • Fallback: Without language code, uses user’s selected language

Date Range Functions

Get precise start and end boundaries for time periods

created_at >= startOfYear(now()) and created_at <= endOfYear(now())
event_time >= startOfMonth("2025-01-01") and event_time <= endOfMonth("2025-01-01")
  • Year: startOfYear(), endOfYear()
  • Month: startOfMonth(), endOfMonth()
  • Day: startOfDay(), endOfDay()
  • Hour: startOfHour(), endOfHour()
  • All functions return precise timestamps with microsecond accuracy

Translation Checks

Find entities with missing translations

Missing Specific Language:

missing 'en' translation

Finds entities where English translation is missing

Missing Any Language:

missing any translation

Finds entities missing translation in any configured language

Combined Conditions:

priority = 'high' and missing 'en' translation

High-priority items needing English translation

In Relations:

count(products[missing 'en' translation]) > 5

Count products missing translations

  • Checks: All translatable fields in the entity
  • Requirements: Primary language must have value
  • Use case: Translation workflow management
  • Languages: Uses organization’s configured languages

Nested Relations with Filters

Filter on relations with sub-conditions

products[category.name = 'Electronics'].price > 100