Expression Builder Guide
How to use the expression builder for complex property transformations. Covers field references, string functions, date functions, mathematical operations, CASE/WHEN conditional logic, and practical examples.
The expression builder allows you to create derived properties in projections. Instead of mapping a property directly from a single source field, you write an expression that combines, transforms, or conditionally selects values from one or more source fields. Expressions are evaluated during projection resolution to produce the final property value.
Field References
Expressions reference source fields using their names. When a property in the source entity is named firstName, you reference it as firstName in the expression. Nested references use dot notation: address.city.
String Functions
| Function | Description | Example | Result |
|---|---|---|---|
| CONCAT(a, b, ...) | Join strings together | CONCAT(firstName, ' ', lastName) | John Smith |
| UPPER(s) | Convert to uppercase | UPPER(status) | ACTIVE |
| LOWER(s) | Convert to lowercase | LOWER(email) | [email protected] |
| TRIM(s) | Remove leading and trailing whitespace | TRIM(name) | John |
| SUBSTRING(s, start, length) | Extract part of a string | SUBSTRING(postCode, 1, 3) | SW1 |
| REPLACE(s, find, replacement) | Replace occurrences in a string | REPLACE(phone, '-', '') | 07712345678 |
| LEFT(s, length) | Extract characters from the start | LEFT(reference, 4) | ORD- |
| RIGHT(s, length) | Extract characters from the end | RIGHT(reference, 6) | 001234 |
| LENGTH(s) | Return the length of a string | LENGTH(description) | 42 |
Date Functions
| Function | Description | Example |
|---|---|---|
| YEAR(d) | Extract the year | YEAR(createdAt) |
| MONTH(d) | Extract the month (1-12) | MONTH(publishedDate) |
| DAY(d) | Extract the day of month | DAY(startDate) |
| DATE_DIFF(d1, d2, unit) | Difference between two dates | DATE_DIFF(endDate, startDate, 'days') |
| DATE_ADD(d, amount, unit) | Add time to a date | DATE_ADD(createdAt, 30, 'days') |
| DATE_FORMAT(d, format) | Format a date as a string | DATE_FORMAT(createdAt, 'YYYY-MM-DD') |
| NOW() | Current date and time | DATE_DIFF(NOW(), lastLogin, 'days') |
Mathematical Operations
| Operation | Syntax | Example |
|---|---|---|
| Addition | a + b | price + tax |
| Subtraction | a - b | grossAmount - discount |
| Multiplication | a * b | quantity * unitPrice |
| Division | a / b | totalScore / questionCount |
| Modulo | a % b | rowNumber % 2 |
| ROUND(n, decimals) | Round to decimal places | ROUND(price * taxRate, 2) |
| FLOOR(n) | Round down to nearest integer | FLOOR(averageRating) |
| CEIL(n) | Round up to nearest integer | CEIL(weight) |
| ABS(n) | Absolute value | ABS(balance) |
CASE/WHEN Conditional Logic
Use CASE/WHEN expressions for conditional property values. This is similar to SQL CASE statements.
The syntax follows this pattern:
CASE WHEN condition1 THEN result1 WHEN condition2 THEN result2 ELSE defaultResult END
Practical Examples
| Derived Property | Expression | Purpose |
|---|---|---|
| fullName | CONCAT(firstName, ' ', lastName) | Combine name parts into a single display name |
| totalPrice | quantity * unitPrice | Calculate line item total |
| discountedPrice | ROUND(price * (1 - discountRate), 2) | Apply percentage discount with rounding |
| ageInDays | DATE_DIFF(NOW(), createdAt, 'days') | Calculate how old a record is |
| displayStatus | CASE WHEN isActive = true THEN 'Active' WHEN deletedAt IS NOT NULL THEN 'Deleted' ELSE 'Inactive' END | Human-readable status label |
| shortReference | CONCAT(LEFT(UPPER(category), 3), '-', RIGHT(id, 6)) | Generate a short reference code |
| financialQuarter | CONCAT('Q', CEIL(MONTH(transactionDate) / 3)) | Derive quarter from transaction date |