Expressions for real-time¶

Amperity Expression Language (AEL) defines how real-time events and profiles are recognized, filtered, and computed. This page describes the subset of AEL used to configure real-time features; it is not a complete AEL reference.

Where expressions are used¶

You write expressions in these real-time settings:

Event streams – a routing expression recognizes each incoming event and determines its event type. A stream may also define expressions for the event timestamp and event ID.
Event types – each field in an event type uses an expression to extract and coerce a value from the raw event payload.
Real-time segments – a segment is defined by a boolean expression that is evaluated against a profile’s attributes.
Real-time attributes – aggregation and computed attributes use expressions over a profile’s events and attributes.

Referencing fields¶

Reference a field by name. Enclose a name that contains spaces or other non-identifier characters in backticks, reach a nested field with dot notation, and reach an array item with bracket notation and a zero-based index:

email
`loyalty tier`
address.city
items[0]

Important

Use bracket notation for array indexes (items[0]). Dot notation followed by a number (items.0) is not valid.

Comparing and combining values¶

Real-time segment expressions and aggregation conditions are boolean expressions. The following operators are available:

Comparison: =, !=, <, <=, >, >=
Membership: in, not in
Null checks: is null, is not null
Logical: and, or, not

Parsing event values¶

Event type field expressions often coerce raw payload values into the field’s type. Commonly used functions include:

to_timestamp and timestamp_from_millis – produce a timestamp from a string or an epoch-milliseconds value.
to_int and to_string – cast a value to an integer or a string.
coalesce – return the first non-null value from a list.

Working with time windows¶

Time-windowed expressions–such as those used in real-time segments and aggregation attributes–take a unit argument. The valid values are SECONDS, MINUTES, and HOURS.

Beyond this subset¶

AEL includes many additional operators and functions–arithmetic, decimal precision, array operations, and more–that are beyond the subset needed for real-time configuration.

AEL operations¶

Each operation below shows how it is written, the type of value it produces, the parameters it accepts, and an example.

Behavior and errors¶

Parameters are required unless marked with an Optional or Repeatable badge.
There is no global NULL rule; each operation documents its own NULL behavior.
Date and time operations are evaluated in UTC.
Value comparisons are case-sensitive, while field-name resolution is case-insensitive.
Logical operators use two-valued logic: NOT NULL is true.
When an expression fails at run time, the expression preview shows an Evaluation error: ... message.

Category	Operations
Comparison	Equal, Greater than, Greater than or equal, Less than, Less than or equal, Not equal
Logical	And, Not, Or
Null	Is not null, Is null
Conditional	Case, Coalesce, Condition, If
Type conversion	To boolean, To date, To decimal, To float, To integer, To string, To timestamp
Collection	Array, Array, struct, or map lookup, Average array, Count by value, Difference, Distinct, Filter, Get element, In, Intersect arrays, Map, Maximum of array, Minimum of array, Most common element, Named struct, Not in, Size, Skip first, Sort array, Struct or map field, Sum array, Take first, Transform, Union, Zip arrays
Text	Concatenate, Concatenate with separator, Contains, Ends with, Lowercase, Replace, SHA-2 hash, Split, Starts with, Uppercase
Numeric	Absolute value, Add, Divide, Maximum, Minimum, Modulus, Multiply, Subtract
Date & time	Add days, Date difference, Date in last, Date in next, Day of week, Day of year, Subtract days, Timestamp difference, Timestamp from milliseconds, Timestamp in last, Timestamp in next, Week of year

Comparison¶

Operations: Equal, Greater than, Greater than or equal, Less than, Less than or equal, Not equal

Equal¶

boolean: value = value

Returns true if all operands are equal to each other.

Example¶

1 = 1
# => true

Greater than¶

boolean: value > value

Returns true if each operand is greater than the operand that follows it.

Warning

Throws an error if the operands are not comparable.

Example¶

3 > 2
# => true

Greater than or equal¶

boolean: value >= value

Returns true if each operand is greater than or equal to the operand that follows it.

Warning

Throws an error if the operands are not comparable.

Example¶

2 >= 2
# => true

Less than¶

boolean: value < value

Returns true if each operand is less than the operand that follows it.

Warning

Throws an error if the operands are not comparable.

Example¶

1 < 2
# => true

Less than or equal¶

boolean: value <= value

Returns true if each operand is less than or equal to the operand that follows it.

Warning

Throws an error if the operands are not comparable.

Example¶

2 <= 2
# => true

Not equal¶

boolean: value != value

Returns true if at least one operand is not equal to the others.

Example¶

1 != 2
# => true

Logical¶

Operations: And, Not, Or

And¶

boolean: value AND value AND …

Returns true if all operands are truthy. Short-circuits evaluation on the first falsey value.

Note

Operands after the selected branch are not evaluated.

Example¶

is_active AND has_subscription

Not¶

boolean: NOT value

Returns true if the operand is falsey.

Example¶

NOT is_active

Or¶

boolean: value OR value OR …

Returns true if any operand is truthy. Short-circuits evaluation on the first truthy value.

Note

Operands after the selected branch are not evaluated.

Example¶

has_browsed_product_l24h OR NOT (has_purchased_product_l24h OR has_activity_l2h)

Null¶

Operations: Is not null, Is null

Is not null¶

boolean: value IS NOT NULL

Returns true if the operand is not null.

Warning

Throws an error if an operand has an unsupported type.

Example¶

'email' IS NOT NULL
# => true

Is null¶

boolean: value IS NULL

Returns true if the operand is null.

Warning

Throws an error if an operand has an unsupported type.

Example¶

NULL IS NULL
# => true

Conditional¶

Operations: Case, Coalesce, Condition, If

Case¶

any: case(value, match, result, …)

Selects a result based on matching a test value against possible cases.

value: Any. The value to match against.
match: Any. A candidate value. Repeatable
result: Any. Result returned when the candidate matches. Repeatable

Note

Operands after the selected branch are not evaluated.

Example¶

case('GOLD', 'GOLD', 0.2, 'SILVER', 0.1, 0.05)
# => 0.2

Coalesce¶

any: coalesce(value, value, …)

Evaluates operands in order, returning the first non-NULL operand.

value: Any. A value to consider. Repeatable

Note

Operands after the selected branch are not evaluated.

Example¶

coalesce(NULL, NULL, 'there')
# => 'there'

Condition¶

any: cond(predicate, result, …)

Evaluates predicates in order, returning the result paired with the first true predicate.

predicate: Boolean. A predicate to evaluate in order. Repeatable
result: Any. Result returned when the predicate is true. Repeatable

Note

Operands after the selected branch are not evaluated.

Example¶

cond('GOLD' = 'GOLD', 0.2, 'SILVER' = 'GOLD', 0.1, 0.05)
# => 0.2

If¶

any: if(condition, then, else)

Returns the second operand if the first operand (condition) is true, otherwise returns the third operand. NULL conditions are treated as false.

condition: Boolean. The condition to test.
then: Any. Result when the condition is true.
else: Any. Result when the condition is false.

Note

Operands after the selected branch are not evaluated.

Example¶

if(100 > 50, 'free', 'standard')
# => 'free'

Type conversion¶

Operations: To boolean, To date, To decimal, To float, To integer, To string, To timestamp

To boolean¶

boolean: to_boolean(value)

Coerces a value to boolean. Returns NULL if coercion is not possible.

value: Any. The value to coerce.

Warning

Throws an error if the value has an unsupported type.

Example¶

to_boolean('true')
# => true

To date¶

date: to_date(value, format)

Coerces a value to date. Returns NULL if coercion is not possible.

value: Any. The value to coerce.
format: String. Date parse format. Optional

Note

Returns NULL if the format pattern is invalid.

Example¶

to_date('2024-01-15', 'yyyy-MM-dd')
# => to_date('2024-01-15')

To decimal¶

decimal: to_decimal(value, precision, scale)

Coerces a value to big decimal, with precision 38 and scale 2 by default. The optional precision and scale parameters must be integer literals. Returns NULL if the coerced value is wider than the precision.

value: Any. The value to coerce.
precision: Integer. Decimal precision, 1 to 38. Must be a literal number. Optional
scale: Integer. Decimal scale, 0 to the precision. Must be a literal number. Optional

Example¶

to_decimal('12.345', 10, 2)
# => to_decimal('12.35')

To float¶

float: to_float(value)

Coerces a value to float. Returns NULL if coercion is not possible.

value: Any. The value to coerce.

Warning

Throws an error if the value has an unsupported type.

Example¶

to_float('12.5')
# => 12.5

To integer¶

integer: to_int(value)

Coerces a value to a 64-bit integer. Returns NULL if coercion is not possible.

value: Any. The value to coerce.

Warning

Throws an error if the value has an unsupported type.

Example¶

to_int('12.9')
# => 12

To string¶

string: to_string(value)

Converts a value to string. Returns NULL if the input is NULL.

value: Any. The value to convert.

Warning

Throws an error if the value has an unsupported type.

Example¶

to_string(123)
# => '123'

To timestamp¶

datetime: to_timestamp(value, format)

Coerces a value to timestamp. Returns NULL if coercion is not possible.

value: Any. The value to coerce.
format: String. Timestamp parse format. Optional

Note

Returns NULL if the format pattern is invalid.

Example¶

to_timestamp('2024-01-15T12:34:56Z')
# => to_timestamp('2024-01-15T12:34:56Z')

Collection¶

Operations: Array, Array, struct, or map lookup, Average array, Count by value, Difference, Distinct, Filter, Get element, In, Intersect arrays, Map, Maximum of array, Minimum of array, Most common element, Named struct, Not in, Size, Skip first, Sort array, Struct or map field, Sum array, Take first, Transform, Union, Zip arrays

Array¶

array: array(value, value, …)

Returns an array of operands in order. All operands must be of the same type.

value: Any. An array element. Repeatable

Example¶

array(1, 2, 3)
# => array(1, 2, 3)

Array, struct, or map lookup¶

any: value[key]

Gets an integer offset from an array, a field from a struct, or an arbitrary key from a map.

Note

Returns NULL if the index is negative or outside the array bounds.

Example¶

array('sku')[0]
# => 'sku'

Average array¶

number: array_avg(array)

Returns the mean of the input array’s non-NULL elements, which must be numeric. Returns NULL if the input is NULL, empty, or holds only NULLs, matching the average aggregation over zero records.

Also accepts the name array_average.

array: Array. The array of numbers to average. For an array of structs, extract a field first with transform().

Example¶

array_avg(array(1, 2, 4))
# => 2.3333333333333335

Count by value¶

array: array_counts(array)

Returns a frequency table for the input array: an array of structs with a value field holding each distinct non-NULL element and a count field holding how many times it occurs. Ordered by count descending, then by the natural sort order of the value, matching the mode aggregation’s tie-break. NULL elements are not counted. Returns NULL if the input is NULL, and an empty array if the input is empty or holds only NULLs.

array: Array. The array to tally. For an array of structs, extract a field first with transform().

Example¶

take(array_counts(array('wool', 'silk', 'wool')), 2)
# => array(map('value', 'wool', 'count', 2), map('value', 'silk', 'count', 1))

Difference¶

array: difference(source, remove)

Returns the elements of the first array that are not present in the second array, preserving the order and duplicates of the first array. A NULL second operand removes nothing, so difference(a, NULL) is a. SparkSQL spells this array_except and removes duplicates; that spelling is not accepted here.

source: Array. The source array.
remove: Array. The array of elements to remove.

Example¶

difference(array('spring', 'summer'), array('summer'))
# => array('spring')

Distinct¶

array: distinct(array)

Returns the input array with duplicate elements removed, preserving the order of first occurrence.

array: Array. The array to deduplicate.

Example¶

distinct(array('spring', 'spring', 'summer'))
# => array('spring', 'summer')

Filter¶

array: filter(array, element -> predicate)

Filters an array using a function that takes one argument (which will be bound to each element in the array, in order) and returns a boolean. Returns an array containing only the elements for which the function returned true.

array: Array. The array to filter.
element -> predicate: Function. A one-argument function whose argument is bound to each element in turn, and which returns a boolean.

Example¶

filter(array('ON', 'OFF'), x -> x = 'ON')
# => array('ON')

Get element¶

any: get(array, index)

Returns the element of the input array at the given (0-based) index. If the index points outside of the array boundaries, or if the index is negative, then this function returns NULL.

array: Array. The array to index into.
index: Integer. The 0-based index.

Example¶

get(array('Merino', 'wool'), 0)
# => 'Merino'

In¶

boolean: value IN (array)

Returns true if the value is one of the listed values. The right operand is a parenthesized list written into the expression, not a field holding an array; to test membership of an array-valued field, use size(filter(field, x -> x = value)) > 0, or size(array_intersect(a, b)) > 0 when both sides are arrays in the record.

Warning

Throws an error if an operand has an unsupported type.

Example¶

'GOLD' IN ('GOLD', 'PLATINUM')
# => true

Intersect arrays¶

array: array_intersect(source, keep)

Returns the elements present in both arrays, with duplicates removed, in the order they first appear in the first array. Returns NULL if either operand is NULL. Matches SparkSQL’s array_intersect on all three points.

source: Array. The source array.
keep: Array. The array of elements to keep.

Example¶

array_intersect(array('a', 'b'), array('b', 'c'))
# => array('b')

Map¶

map: map(key, value, …)

Returns a map of the given key/value pairs. All keys and all values must be of the same type.

key: Any. A map key. Repeatable
value: Any. A map value. Repeatable

Example¶

map('sku', 'ABC', 'brand', 'Wool')
# => map('sku', 'ABC', 'brand', 'Wool')

Maximum of array¶

any: array_max(array)

Returns the largest of the input array’s non-NULL elements. Unlike the scalar max, the elements need only be orderable, so arrays of strings, dates, and timestamps are supported. Returns NULL if the input is NULL, empty, or holds only NULLs.

array: Array. The array to take the maximum of. For an array of structs, extract a field first with transform().

Example¶

array_max(array(1, 2, 3))
# => 3

Minimum of array¶

any: array_min(array)

Returns the smallest of the input array’s non-NULL elements. Unlike the scalar min, the elements need only be orderable, so arrays of strings, dates, and timestamps are supported. Returns NULL if the input is NULL, empty, or holds only NULLs.

array: Array. The array to take the minimum of. For an array of structs, extract a field first with transform().

Example¶

array_min(array(1, 2, 3))
# => 1

Most common element¶

any: array_mode(array)

Returns the most frequently occurring element of the input array, or NULL if the input is NULL or contains no non-NULL elements. NULL elements are not counted. Ties are broken by the natural sort order of the tied elements, matching the mode aggregation.

array: Array. The array to reduce. For an array of structs, extract a field first with transform().

Example¶

array_mode(array('wool', 'silk', 'wool'))
# => 'wool'

Named struct¶

struct: named_struct(key, value, …)

Returns a struct with the given field names and values. Each name must be written as text, must be a valid field name, and may not be repeated.

key: String. A struct field name, written directly as text. Letters, numbers, and underscores, with at least one letter or underscore, and unique within the struct. Repeatable
value: Any. A struct field value. Repeatable

Example¶

named_struct('sku', 'ABC', 'count', 2)
# => map('sku', 'ABC', 'count', 2)

Not in¶

boolean: value NOT IN (array)

Returns true if the value is none of the listed values. The right operand is a parenthesized list written into the expression, not a field holding an array; to test membership of an array-valued field, use size(filter(field, x -> x = value)) = 0.

Warning

Throws an error if an operand has an unsupported type.

Example¶

'SILVER' NOT IN ('GOLD', 'PLATINUM')
# => true

Size¶

integer: size(collection)

Returns the integer element count of the operand, or NULL if the operand is NULL.

collection: Array. The collection to measure.

Warning

Throws an error if the operand is not a collection.

Example¶

size(array('Merino', 'wool'))
# => 2

Skip first¶

array: skip(array, offset)

Returns the input array without its first offset elements. An offset at or past the end of the array returns an empty array. Returns NULL if either operand is NULL or if offset is negative. Compose with take for a window out of the middle: take(skip(arr, 1), 2).

array: Array. The array to skip into.
offset: Integer. How many leading elements to skip.

Example¶

skip(array(1, 2, 3), 1)
# => array(2, 3)

Sort array¶

array: array_sort(array, comparator)

Sorts the input array, ascending by default, placing NULL elements at the end and ordering NaN above every other float. A comparator sorts by any other order: it takes two elements and returns a negative number, zero, or a positive number as the first sorts before, with, or after the second, and returning NULL from it fails the expression. An array of structs has no default order, so it requires a comparator.

array: Array. The array to sort.
comparator: Function. A two-argument comparator function. Optional

Warning

Throws an error if a comparator returns NULL.

Example¶

array_sort(array(3, 1, 2))
# => array(1, 2, 3)

Struct or map field¶

any: value.key

Gets a field from a struct, or a string key from a map.

Example¶

map('city', 'Seattle').city
# => 'Seattle'

Sum array¶

number: array_sum(array)

Returns the sum of the input array’s non-NULL elements, which must be numeric. Returns 0 for an empty array or one holding only NULLs, matching the sum aggregation over zero records. Returns NULL if the input is NULL.

array: Array. The array of numbers to add. For an array of structs, extract a field first with transform().

Example¶

array_sum(array(1, 2, 3))
# => 6

Take first¶

array: take(array, limit)

Returns the first limit elements of the input array. A limit longer than the array returns the whole array, and a limit of zero returns an empty array. Returns NULL if either operand is NULL or if limit is negative.

array: Array. The array to take from.
limit: Integer. How many elements to take.

Example¶

take(array(1, 2, 3), 2)
# => array(1, 2)

Transform¶

array: transform(array, element -> expression)

Transforms an array using a function that takes each element of the array, in order, and returns a new value. Returns an array of the values returned by the function. The function may take an optional second argument, which will be bound to the zero-based index of the element.

array: Array. The array to transform.
element -> expression: Function. A function whose first argument is bound to each element in turn. An optional second argument is bound to that element’s 0-based index: (element, index) -> expression.

Example¶

transform(array('wool', 'silk'), x -> upper(x))
# => array('WOOL', 'SILK')

Union¶

array: union(array, array, …)

Concatenates two or more arrays into a single array, preserving order and keeping duplicate elements. All operands must be arrays of primitive or flat-struct elements with a common element type. NULL operands are skipped rather than propagated, so union(a, NULL) is a. SparkSQL spells this array_union and removes duplicates; that spelling is not accepted here.

array: Array. An array to concatenate. Repeatable

Example¶

union(array('spring'), array('summer'))
# => array('spring', 'summer')

Zip arrays¶

array: array_zip(name, array, name, array, …)

Combines two or more arrays element-wise into an array of structs. Shorter arrays are padded with NULL, so the result length is that of the longest input, and the result is NULL if any input array is NULL – both as in SparkSQL’s arrays_zip. Operands alternate between a field name and the array supplying that field’s values, as in named_struct: SparkSQL derives struct field names from the input column names, and an AEL operand is an expression with no name to derive.

name: String. A struct field name, written directly as text. Repeatable
array: Array. The array supplying that field’s values. Repeatable

Example¶

array_zip('sku', array('A', 'B'), 'brand', array('Wool', 'Silk'))
# => array(map('sku', 'A', 'brand', 'Wool'), map('sku', 'B', 'brand', 'Silk'))

Text¶

Operations: Concatenate, Concatenate with separator, Contains, Ends with, Lowercase, Replace, SHA-2 hash, Split, Starts with, Uppercase

Concatenate¶

string: concat(value, value, …)

Concatenates operands together with no spaces/separator.

value: String. A value to join. Repeatable

Warning

Throws an error if an operand has an unsupported type.

Example¶

concat('Merino', ' ', 'wool')
# => 'Merino wool'

Concatenate with separator¶

string: concat_ws(separator, value, …)

Concatenates operands together. The first operand should be a (possibly empty) separator string.

separator: String. The separator placed between values.
value: String. A value to join. Repeatable

Warning

Throws an error if an operand has an unsupported type.

Example¶

concat_ws('-', 'Merino', 'wool')
# => 'Merino-wool'

Contains¶

boolean: contains(string, substring)

Returns true if the first operand string contains the second operand string.

string: String. The string to search.
substring: String. The substring to look for.

Warning

Throws an error if an operand has an unsupported type.

Example¶

contains('Merino wool blend', 'Merino')
# => true

Ends with¶

boolean: endswith(string, suffix)

Returns true if the first operand string ends with the second operand string.

string: String. The string to test.
suffix: String. The suffix to look for.

Warning

Throws an error if an operand has an unsupported type.

Example¶

endswith('receipt@example.com', '@example.com')
# => true

Lowercase¶

string: lower(string)

Converts the operand string to lower case.

string: String. The string to convert.

Warning

Throws an error if an operand has an unsupported type.

Example¶

lower('Merino')
# => 'merino'

Replace¶

string: replace(string, search, replacement)

Replaces all occurrences of the second operand string in the first operand string with the third operand string.

string: String. The string to operate on.
search: String. The substring to replace.
replacement: String. The replacement substring. Optional

Warning

Throws an error if an operand has an unsupported type.

Example¶

replace('555-1234', '-', '')
# => '5551234'

SHA-2 hash¶

string: sha2(string, bits)

Returns a SHA-2 checksum of the input string, as a hex string. SHA-224, SHA-256, SHA-384, and SHA-512 are supported. Bit length of 0 is equivalent to 256.

string: String. The string to hash.
bits: Integer. Bit length: 224, 256, 384, or 512 (0 means 256).

Note

Returns NULL if the bit length is not 0, 224, 256, 384, or 512.

Example¶

sha2('abc', 256)
# => 'ba7816bf8f01cfea414140de5dae2223b00361a396177a9cb410ff61f20015ad'

Split¶

array: split(string, delimiter)

Splits the first operand string on each literal occurrence of the second operand string, returning an array of strings. The delimiter is matched literally, not as a regular expression. Empty trailing elements are preserved, so the result length is stable for fixed-shape values. An empty delimiter splits the string into one element per code point, matching SparkSQL.

string: String. The string to split.
delimiter: String. The literal delimiter to split on. An empty delimiter splits into single characters.

Example¶

split('Merino wool', ' ')[0]
# => 'Merino'

Starts with¶

boolean: startswith(string, prefix)

Returns true if the first operand string starts with the second operand string.

string: String. The string to test.
prefix: String. The prefix to look for.

Warning

Throws an error if an operand has an unsupported type.

Example¶

startswith('Merino wool blend', 'Merino')
# => true

Uppercase¶

string: upper(string)

Converts the operand string to upper case.

string: String. The string to convert.

Warning

Throws an error if an operand has an unsupported type.

Example¶

upper('wool')
# => 'WOOL'

Numeric¶

Operations: Absolute value, Add, Divide, Maximum, Minimum, Modulus, Multiply, Subtract

Absolute value¶

number: abs(number)

Returns the absolute value of the operand.

number: Number. The number whose absolute value to return.

Warning

Throws an error if the operand is NULL.

Example¶

abs(-5)
# => 5

Add¶

number: value + value + …

Adds operands.

Warning

Throws an error if the operands exceed the numeric range the operation supports.

Example¶

1 + 2
# => 3

Divide¶

number: value / value / …

Divides operands. Treats division by zero as a NULL result.

Note

Returns NULL if a divisor is zero.

Example¶

6 / 2
# => 3.0

Maximum¶

number: max(value, value, …)

Returns the maximum value from operands, ignoring NULL values.

value: Number. A value to compare. Repeatable

Example¶

max(3, 1)
# => 3

Minimum¶

number: min(value, value, …)

Returns the minimum value from operands, ignoring NULL values.

value: Number. A value to compare. Repeatable

Example¶

min(3, 1)
# => 1

Modulus¶

number: dividend % divisor

Returns the modulus of the first operand divided by the second operand.

Also accepts the name mod.

Warning

Throws an error if either operand is NULL or the divisor is zero.

Example¶

10 % 3
# => 1

Multiply¶

number: value * value * …

Multiplies operands.

Warning

Throws an error if the operands exceed the numeric range the operation supports.

Example¶

3 * 4
# => 12

Subtract¶

number: value - value - …

Subtracts operands.

Warning

Throws an error if the operands exceed the numeric range the operation supports.

Example¶

5 - 2
# => 3

Date & time¶

Operations: Add days, Date difference, Date in last, Date in next, Day of week, Day of year, Subtract days, Timestamp difference, Timestamp from milliseconds, Timestamp in last, Timestamp in next, Week of year

Add days¶

date: date_add(date, days)

Adds the specified number of days to a date.

date: Date. The starting date.
days: Integer. Number of days to add.

Warning

Throws an error if an operand has an unsupported type.

Example¶

date_add(to_date('2024-01-15'), 30)
# => to_date('2024-02-14')

Date difference¶

integer: date_diff(start, end)

Returns the absolute number of days between two dates.

start: Date. The first date.
end: Date. The second date.

Warning

Throws an error if either date is NULL or an operand has an unsupported type.

Example¶

date_diff(to_date('2024-01-15'), to_date('2024-01-18'))
# => 3

Date in last¶

boolean: date_in_last(days, date)

Returns true if the date falls within the past n days of today. A value of zero matches only today, a value of one also matches yesterday, and so on.

days: Integer. Number of days in the window.
date: Date. The date to test.

Warning

Throws an error if an operand has an unsupported type.

Note

Evaluated against the current time, so the result changes as time passes.

Example¶

date_in_last(30, to_date('2024-01-15'))

Date in next¶

boolean: date_in_next(days, date)

Returns true if the date falls within the next n days from today. A value of zero matches only today, a value of one also matches tomorrow, and so on.

days: Integer. Number of days in the window.
date: Date. The date to test.

Warning

Throws an error if an operand has an unsupported type.

Note

Evaluated against the current time, so the result changes as time passes.

Example¶

date_in_next(7, to_date('2024-01-15'))

Day of week¶

integer: day_of_week(date)

Returns the ISO day of week for the date, from 1 for Monday through 7 for Sunday.

date: Date. The date to evaluate.

Warning

Throws an error if the date has an unsupported type.

Example¶

day_of_week(to_date('2024-03-04')) >= 1
# => true

Day of year¶

integer: day_of_year(date)

Returns the day of the year for the date, from 1 to 366.

date: Date. The date to evaluate.

Warning

Throws an error if the date has an unsupported type.

Example¶

day_of_year(to_date('2024-03-01'))
# => 61

Subtract days¶

date: date_sub(date, days)

Subtracts the specified number of days from a date.

date: Date. The starting date.
days: Integer. Number of days to subtract.

Warning

Throws an error if an operand has an unsupported type.

Example¶

date_sub(to_date('2024-01-15'), 7)
# => to_date('2024-01-08')

Timestamp difference¶

integer: timestamp_diff(unit, start, end)

Returns the whole number of units between two timestamps.

unit: String. A quoted, case-insensitive string literal: ‘seconds’, ‘minutes’, or ‘hours’.
start: Datetime. The first timestamp.
end: Datetime. The second timestamp.

Warning

Throws an error if:

either timestamp is NULL
the unit is not one of ‘seconds’, ‘minutes’, or ‘hours’
a timestamp has an unsupported type

Example¶

timestamp_diff('hours', to_timestamp('2024-01-15T00:00:00Z'), to_timestamp('2024-01-15T03:00:00Z'))
# => 3

Timestamp from milliseconds¶

datetime: timestamp_from_millis(millis)

Coerces milliseconds since the Unix epoch (1970-01-01T00:00:00Z), given as a number, to a timestamp. Returns NULL for NULL, non-numeric, or out-of-range input.

For numeric epoch fields in event payloads (e.g. GA4 event_timestamp) that :to-timestamp cannot parse.

millis: Integer. Milliseconds since 1970-01-01T00:00:00Z.

Warning

Throws an error if the value has an unsupported type.

Example¶

timestamp_from_millis(0)
# => to_timestamp('1970-01-01T00:00:00Z')

Timestamp in last¶

boolean: timestamp_in_last(n, unit, timestamp)

Returns true if the timestamp falls within the past n units of the current time.

n: Integer. Size of the window.
unit: String. A quoted, case-insensitive string literal: ‘seconds’, ‘minutes’, or ‘hours’.
timestamp: Datetime. The timestamp to test.

Warning

Throws an error if an operand has an unsupported type.

Returns NULL if the window size is zero.

Note

Evaluated against the current time, so the result changes as time passes.

Example¶

timestamp_in_last(2, 'hours', to_timestamp('2024-01-15T00:00:00Z'))

Timestamp in next¶

boolean: timestamp_in_next(n, unit, timestamp)

Returns true if the timestamp falls within the next n units from the current time.

n: Integer. Size of the window.
unit: String. A quoted, case-insensitive string literal: ‘seconds’, ‘minutes’, or ‘hours’.
timestamp: Datetime. The timestamp to test.

Warning

Throws an error if an operand has an unsupported type.

Returns NULL if the window size is zero.

Note

Evaluated against the current time, so the result changes as time passes.

Example¶

timestamp_in_next(2, 'hours', to_timestamp('2024-01-15T00:00:00Z'))

Week of year¶

integer: week_of_year(date)

Returns the ISO-8601 week of the week-based year for the date, from 1 to 53. Weeks start on Monday, and week 1 is the one containing the year’s first Thursday.

date: Date. The date to evaluate.

Warning

Throws an error if the date has an unsupported type.

Example¶

week_of_year(to_date('2024-01-04'))
# => 1

Choose topic collections

Expressions for real-time¶

Where expressions are used¶

Referencing fields¶

Comparing and combining values¶

Parsing event values¶

Working with time windows¶

Beyond this subset¶

AEL operations¶

Behavior and errors¶

Comparison¶

Equal¶

Greater than¶

Greater than or equal¶

Less than¶

Less than or equal¶

Not equal¶

Logical¶

And¶

Not¶

Or¶

Null¶

Is not null¶

Is null¶

Conditional¶

Case¶

Coalesce¶

Condition¶

If¶

Type conversion¶

To boolean¶

To date¶

To decimal¶

To float¶

To integer¶

To string¶

To timestamp¶

Collection¶

Array¶

Array, struct, or map lookup¶

Average array¶

Count by value¶

Difference¶

Distinct¶

Filter¶

Get element¶

In¶

Intersect arrays¶

Map¶

Maximum of array¶

Minimum of array¶

Most common element¶

Named struct¶

Not in¶

Size¶

Skip first¶

Sort array¶

Struct or map field¶

Sum array¶

Take first¶

Transform¶

Union¶

Zip arrays¶

Text¶

Concatenate¶

Concatenate with separator¶

Contains¶

Ends with¶

Lowercase¶

Replace¶

SHA-2 hash¶

Split¶

Starts with¶

Uppercase¶

Numeric¶

Absolute value¶

Add¶

Divide¶

Maximum¶

Minimum¶