OQL Functions Reference ⚡

Functions Overview

Navigation

Use the Index below to jump to the details of a function.

📅 Date & Time

Function	Purpose
`adjustTimeToInterval`	Snap a timestamp to the start or end of a named interval (e.g., shift).
`date_trunc`	Truncate a timestamp to the start of a bucket (s, m, h, D, W, M, Q, Y).
`timeinif`	Seconds within the interval where a condition holds true.
`toDateTime64`	Format a timestamp as a date-time string with precision and timezone.
`toUnixTimestamp`	Date-time to Unix epoch seconds.
`age`	Calculate difference between two dates.
`addDays`	Add days to a date.
`addHours`	Add hours to a date.
`addMinutes`	Add minutes to a date.
`formatDateTime`	Format a date-time using a template.

📊 Metric & Aggregate

Function	Purpose
`abs`	Absolute value.
`avg`	Average of values.
`avgWeighted`	Duration-weighted average (alias for weighted avg).
`mean`	Alias for `avg`.
`avgIf`	Conditional average.
`boundingRatio`	Slope between first and last values over time.
`deltasum`	Sum of positive increments between consecutive points.
`durationWeightedAvg`	Duration‑weighted average.
`exp`	e^x.
`first`	First value in range.
`firstIf`	First value meeting a condition.
`intDiv`	Integer division.
`last`	Last value in range.
`lastIf`	Last value meeting a condition.
`log`	Natural log.
`max`	Maximum value in a timeseries.
`metric`	Read a metric by UUID.
`metric_group`	Read a metric group by UUID.
`metric_group_metadata`	Read display metadata for a metric group.
`min`	Minimum value in a timeseries.
`mod`	Modulus (remainder).
`p05`	5th percentile.
`p05If`	Conditional 5th percentile.
`p10`	10th percentile.
`p10If`	Conditional 10th percentile.
`p20`	20th percentile.
`p20If`	Conditional 20th percentile.
`p25`	25th percentile.
`p25If`	Conditional 25th percentile.
`p50`	Median (50th percentile).
`p50If`	Conditional median.
`p75`	75th percentile.
`p75If`	Conditional 75th percentile.
`p80`	80th percentile.
`p80If`	Conditional 80th percentile.
`p90`	90th percentile.
`p90If`	Conditional 90th percentile.
`p95`	95th percentile.
`p95If`	Conditional 95th percentile.
`p*TDigest`	Approximate quantiles using TDigest.
`p*TDigestIf`	Conditional TDigest quantiles.
`stddev`	Standard deviation.
`sum`	Sum of values.
`sum2`	Sum of squares.
`sumIf`	Conditional sum.
`count`	Distinct count.
`countIf`	Conditional count on metrics, or interval count when not applied to a metric.
`date_diff`	Difference between two datetimes in a given unit.
`lag`	Previous row value (window function).
`lead`	Next row value (window function).

➕ Math

Function	Purpose
`coalesce`	First non‑null value.
`greatest`	Largest of multiple values.
`least`	Smallest of multiple values.
`pow`	Power function.
`round`	Round to a decimal place or magnitude.
`sqrt`	Square root.
`floor`	Round down to nearest integer or decimal place.
`ceil`	Round up to nearest integer or decimal place.
`trunc`	Truncate to integer or decimal place.
`sign`	Sign of number (-1, 0, or 1).
`log10`	Base-10 logarithm.
`log2`	Base-2 logarithm.
`cbrt`	Cube root.
`clamp`	Constrain value to range.
`isFinite`	Check if number is finite.

🔀 Conditional

Function	Purpose
`if`	If/else.
`multiIf`	Multi‑branch conditional.
`isNaN`	Is NaN.
`isNull`	Is null.
`isNotNull`	Not null.
`isZeroOrNull`	Is zero or null.

🔤 String

Function	Purpose
`concat`	Concatenate strings.
`containsAny`	Contains any of the given values.
`like`	match with wildcards.
`match`	Regex match.
`left`	Left substring.
`right`	Right substring.
`substring`	Substring by position and length.
`length`	Length of string.

📄 JSON

Function	Purpose
`jsonExists`	Check if path exists in JSON.
`jsonQuery`	Extract object/array by path.
`jsonValue`	Extract scalar value by path.

♻️ Type Conversion

Function	Purpose
`cast`	Convert value to a type.
`castOrNull`	Safe cast (returns null if invalid).
`toString`	Convert to string.
`toInt32`	Convert to 32-bit integer.
`toInt64`	Convert to 64-bit integer.
`toFloat32`	Convert to 32-bit float.
`toFloat64`	Convert to 64-bit float.
`toDate`	Convert to date.

🛠️ Misc

Function	Purpose
`all`	Array of all values in interval.
`encodeURLComponent`	URL‑safe encoding.
`formatRow`	Format row output (CSV/TSV/JSONCompactEachRow).
`set`	List of unique values for metrics and intervals.
`has`	Array membership test.
`hasAny`	Array contains any of given values.
`hasAll`	Array contains all given values.
`exponentialTimeDecayedAvg`	Exponentially weighted moving average.
—

Function Details

`• adjustTimeToInterval`

Snaps time to the boundary of interval (start or end). Useful for aligning queries to shifts or custom windows.

Arguments

Name	Type	Description
`time`	DateTime	Input timestamp (e.g., `now()`).
`interval`	String	Interval descriptor (e.g., `shift.start`, `shift.end`).

💡 Example

where time from now() to adjustTimeToInterval(now(), shift.start)

🔗 See also date_trunc • toDateTime64 • toUnixTimestamp

`• date_trunc`

Rounds time down to the start of a given bucket (e.g., day or week).

Arguments

Name	Type	Description
`time`	DateTime	Input timestamp (e.g., `now()`).
`bucket`	String	One of `s, m, h, D, W, M, Q, Y`.

💡 Example

where time from now() to date_trunc(now(),'D')

🔗 See also adjustTimeToInterval • toUnixTimestamp • toDateTime64

`• timeinif`

Counts the number of seconds in the current range where condition is true. With no arguments, returns the total query duration in seconds.

Arguments

Name	Type	Description
`condition`	Boolean (optional)	Predicate evaluated over time. Omit for total window duration.

💡 Example

aggregate timeinif(interval.state.category.name = 'Uptime')

🔗 See also sumIf • avgIf • countIf

`• toDateTime64`

Converts timestamp to a DateTime with precision fractional seconds in timezone.

Arguments

Name	Type	Description
`timestamp`	DateTime / UnixSeconds	Source timestamp (e.g., `query.start`).
`precision`	Integer	Fractional second precision (0–3).
`timezone`	String	IANA timezone (e.g., `'America/New_York'`).

Example

aggregate toDateTime64(query.start, 0, 'America/Los_Angeles')

🔗 See also toUnixTimestamp • date_trunc • adjustTimeToInterval

`• toUnixTimestamp`

Returns Unix seconds for a given date_time.

Arguments

Name	Type	Description
`date_time`	DateTime	Input date-time (e.g., `query.end`).

💡 Example

aggregate toUnixTimestamp(query.end)

🔗 See also toDateTime64 • date_trunc • adjustTimeToInterval

`• abs`

Returns the non‑negative value of expression.

Arguments

Name	Type	Description
`expression`	Number / Timeseries	Input value.

💡 Example

aggregate abs(avg(metric_group('uuid')))

🔗 See also sqrt • pow • log

`• avg`

Computes the arithmetic mean of expression.

Arguments

Name	Type	Description
`expression`	Number / Timeseries	Values to average.

💡 Example

aggregate avg(metric_group('uuid'))

🔗 See also avgIf • sum • count

`• avgIf`

Average of expression for rows where condition is true.

Arguments

Name	Type	Description
`expression`	Number / Timeseries	Values to average.
`condition`	Boolean	Filter predicate.

💡 Example

aggregate avgIf(metric_group('uuid'), interval.state.category.name = 'Uptime')

🔗 See also avg • sumIf • countIf

`• boundingRatio`

Computes (last − first) ÷ (end_time − start_time) for expression.

Arguments

Name	Type	Description
`expression`	Number / Timeseries	Series to evaluate.

💡 Example

aggregate boundingRatio(metric_group('uuid'))

🔗 See also deltasum • first • last

`• deltasum`

Sums positive increases of expression across time; ignores decreases.

Arguments

Name	Type	Description
`expression`	Number / Timeseries	Monotonic or counter-like series.

💡 Example

aggregate deltasum(metric_group('uuid'))

🔗 See also sum • boundingRatio • countIf

`• durationWeightedAvg`

Definition Averages expression with weights proportional to interval duration.

Arguments

Name	Type	Description
`expression`	Number / Timeseries	Values to average with time weighting.

💡 Example

aggregate durationWeightedAvg(interval.run.performance_target.value)

🔗 See also avg • avgIf • sumIf

`• exp`

Raises Euler’s number e to expression.

Arguments

Name	Type	Description
`expression`	Number / Timeseries	Exponent input.

💡 Example

aggregate exp(last(metric_group('uuid')))

🔗 See also log • pow • sqrt

`• first`

Returns the earliest value of expression in the selected time range.

Arguments

Name	Type	Description
`expression`	Any / Timeseries	Series or field.

💡 Example

aggregate first(metric_group('uuid'))

🔗 See also last • firstIf • coalesce

`• firstIf`

Returns the earliest expression where condition is true.

Arguments

Name	Type	Description
`expression`	Any / Timeseries	Series or field.
`condition`	Boolean	Filter predicate.

💡 Example

aggregate firstIf(offline_quality.results.TEST_NAME, offline_quality.results.METADATA = 'X')

🔗 See also lastIf • first • coalesce

`• intDiv`

Divides lhs by rhs and truncates toward zero.

Arguments

Name	Type	Description
`lhs`	Number	Dividend.
`rhs`	Number	Divisor (non‑zero).

💡 Example

aggregate intDiv(7, 2)

🔗 See also mod • round • pow

`• last`

Returns the most recent value of expression in the selected time range.

Arguments

Name	Type	Description
`expression`	Any / Timeseries	Series or field.

💡 Example

aggregate last(metric_group('uuid'))

🔗 See also first • lastIf • coalesce

`• lastIf`

Returns the most recent expression where condition is true.

Arguments

Name	Type	Description
`expression`	Any / Timeseries	Series or field.
`condition`	Boolean	Filter predicate.

💡 Example

aggregate lastIf(offline_quality.results.TEST_NAME, offline_quality.results.METADATA = 'X')

🔗 See also firstIf • last • coalesce

`• log`

Computes ln(expression).

Arguments

Name	Type	Description
`expression`	Number / Timeseries	Positive input value.

💡 Example

aggregate log(10)

🔗 See also exp • pow • sqrt

`• max`

Returns the largest value of expression over the range.

Arguments

Name	Type	Description
`expression`	Number / Timeseries	Values to compare.

💡 Example

aggregate max(metric_group('uuid'))

🔗 See also min • greatest • p95

`• metric`

Fetches values of the metric identified by UUID.

Arguments

Name	Type	Description
`uuid`	String	Metric identifier.

💡 Example

aggregate avg(metric('uuid'))

🔗 See also metric_group • avg

`• metric_group`

Fetches values of the metric group identified by UUID.

Arguments

Name	Type	Description
`uuid`	String	Metric group identifier.

💡 Example

aggregate avg(metric_group('uuid'))

🔗 See also metric • sum

`• metric_group_metadata`

Returns display metadata (e.g., display name) for a metric group field.

Arguments

Name	Type	Description
`field`	String	Metadata field key (e.g., `'performance'`).

💡 Example

aggregate metric_group_metadata('performance').display_name

🔗 See also metric_group • toString • formatRow

`• min`

Returns the smallest value of expression over the range.

Arguments

Name	Type	Description
`expression`	Number / Timeseries	Values to compare.

💡 Example

aggregate min(metric_group('uuid'))

🔗 See also max • least • p25

`• mod`

Returns the remainder of lhs divided by rhs.

Arguments

Name	Type	Description
`lhs`	Number	Dividend.
`rhs`	Number	Divisor (non‑zero).

💡 Example

aggregate mod(7, 2)

🔗 See also intDiv • round • abs

`• p05`

Returns the 0.05 quantile of expression.

Arguments

Name	Type	Description
`expression`	Number / Timeseries	Values to aggregate.

💡 Example

aggregate p05(metric_group('uuid'))

🔗 See also p05If • p25 • p*TDigest

`• p05If`

Returns the 5th percentile of expression where condition is true.

Arguments

Name	Type	Description
`expression`	Number / Timeseries	Values to aggregate.
`condition`	Boolean	Filter predicate.

💡 Example

aggregate p05If(metric_group('uuid'), interval.state.category.name = 'Uptime')

🔗 See also p05 • p25If • p*TDigestIf

`• p10`

Returns the 0.10 quantile of expression.

Arguments

Name	Type	Description
`expression`	Number / Timeseries	Values to aggregate.

💡 Example

aggregate p10(metric_group('uuid'))

🔗 See also p10If • p20 • p*TDigest

`• p10If`

Returns the 10th percentile of expression where condition is true.

Arguments

Name	Type	Description
`expression`	Number / Timeseries	Values to aggregate.
`condition`	Boolean	Filter predicate.

💡 Example

aggregate p10If(metric_group('uuid'), interval.state.category.name = 'Uptime')

🔗 See also p10 • p20If • p*TDigestIf

`• p20`

Returns the 0.20 quantile of expression.

Arguments

Name	Type	Description
`expression`	Number / Timeseries	Values to aggregate.

💡 Example

aggregate p20(metric_group('uuid'))

🔗 See also p20If • p25 • p*TDigest

`• p20If`

Returns the 20th percentile of expression where condition is true.

Arguments

Name	Type	Description
`expression`	Number / Timeseries	Values to aggregate.
`condition`	Boolean	Filter predicate.

💡 Example

aggregate p20If(metric_group('uuid'), interval.state.category.name = 'Uptime')

🔗 See also p20 • p25If • p*TDigestIf

`• p25`

Returns the 0.25 quantile of expression.

Arguments

Name	Type	Description
`expression`	Number / Timeseries	Values to aggregate.

💡 Example

aggregate p25(metric_group('uuid'))

🔗 See also p25If • p50 • p*TDigest

`• p25If`

Returns the 25th percentile of expression where condition is true.

Arguments

Name	Type	Description
`expression`	Number / Timeseries	Values to aggregate.
`condition`	Boolean	Filter predicate.

💡 Example

aggregate p25If(metric_group('uuid'), interval.state.category.name = 'Uptime')

🔗 See also p25 • p50If • p*TDigestIf

`• p50`

Returns the median (0.5 quantile) of expression.

Arguments

Name	Type	Description
`expression`	Number / Timeseries	Values to aggregate.

💡 Example

aggregate p50(metric_group('uuid'))

🔗 See also p50If • p25 • p75

`• p50If`

Returns the median of expression where condition is true.

Arguments

Name	Type	Description
`expression`	Number / Timeseries	Values to aggregate.
`condition`	Boolean	Filter predicate.

💡 Example

aggregate p50If(metric_group('uuid'), interval.state.category.name = 'Uptime')

🔗 See also p50 • p25If • p75If

`• p75`

Returns the 0.75 quantile of expression.

Arguments

Name	Type	Description
`expression`	Number / Timeseries	Values to aggregate.

💡 Example

aggregate p75(metric_group('uuid'))

🔗 See also p75If • p50 • p90

`• p75If`

Returns the 75th percentile where condition is true.

Arguments

Name	Type	Description
`expression`	Number / Timeseries	Values to aggregate.
`condition`	Boolean	Filter predicate.

💡 Example

aggregate p75If(metric_group('uuid'), interval.state.category.name = 'Uptime')

🔗 See also p75 • p50If • p90If

`• p80`

Returns the 0.80 quantile of expression.

Arguments

Name	Type	Description
`expression`	Number / Timeseries	Values to aggregate.

💡 Example

aggregate p80(metric_group('uuid'))

🔗 See also p80If • p75 • p90

`• p80If`

Returns the 80th percentile where condition is true.

Arguments

Name	Type	Description
`expression`	Number / Timeseries	Values to aggregate.
`condition`	Boolean	Filter predicate.

💡 Example

aggregate p80If(metric_group('uuid'), interval.state.category.name = 'Uptime')

🔗 See also p80 • p75If • p90If

`• p90`

Returns the 0.90 quantile of expression.

Arguments

Name	Type	Description
`expression`	Number / Timeseries	Values to aggregate.

💡 Example

aggregate p90(metric_group('uuid'))

🔗 See also p90If • p75 • p95

`• p90If`

Returns the 90th percentile where condition is true.

Arguments

Name	Type	Description
`expression`	Number / Timeseries	Values to aggregate.
`condition`	Boolean	Filter predicate.

💡 Example

aggregate p90If(metric_group('uuid'), interval.state.category.name = 'Uptime')

🔗 See also p90 • p75If • p95If

`• p95`

Returns the 0.95 quantile of expression.

Arguments

Name	Type	Description
`expression`	Number / Timeseries	Values to aggregate.

💡 Example

aggregate p95(metric_group('uuid'))

🔗 See also p95If • p90 • p*TDigest

`• p95If`

Returns the 95th percentile where condition is true.

Arguments

Name	Type	Description
`expression`	Number / Timeseries	Values to aggregate.
`condition`	Boolean	Filter predicate.

💡 Example

aggregate p95If(metric_group('uuid'), interval.state.category.name = 'Uptime')

🔗 See also p95 • p90If • p*TDigestIf

`• p*TDigest`

Approximate p‑quantiles (p25TDigest, p75TDigest) for expression using TDigest (memory‑efficient).

Arguments

Name	Type	Description
`expression`	Number / Timeseries	Values to aggregate.

💡 Example

aggregate p75TDigest(metric_group('uuid'))

🔗 See also p*TDigestIf • p75 • p90

`• p*TDigestIf`

Approximate p‑quantiles (p25TDigestIf, p75TDigestIf) for rows where condition is true.

Arguments

Name	Type	Description
`expression`	Number / Timeseries	Values to aggregate.
`condition`	Boolean	Filter predicate.

💡 Example

aggregate p90TDigestIf(metric_group('uuid'), interval.state.category.name = 'Uptime')

🔗 See also p*TDigest • p90If • p75If

`• pow`

Computes base raised to exponent.

Arguments

Name	Type	Description
`base`	Number	Base value.
`exponent`	Number	Exponent.

💡 Example

aggregate pow(metric_group('uuid'), 3)

🔗 See also exp • sqrt • log

`• round`

Rounds value to digits decimal places (negative digits round to tens/hundreds).

Arguments

Name	Type	Description
`value`	Number	Input value.
`digits`	Integer	Decimal places; may be negative.

💡 Example

aggregate round(metric_group('uuid'), -1)

🔗 See also intDiv • mod • pow

`• sqrt`

Returns the principal square root of value.

Arguments

Name	Type	Description
`value`	Number	Non‑negative input.

💡 Example

aggregate sqrt(metric_group('uuid'))

🔗 See also pow • abs • log

`• stddev`

Returns the standard deviation of expression.

Arguments

Name	Type	Description
`expression`	Number / Timeseries	Values to aggregate.

💡 Example

aggregate stddev(metric_group('uuid'))

🔗 See also avg • sum2 • p95

`• sum`

Sums all values of expression.

Arguments

Name	Type	Description
`expression`	Number / Timeseries	Values to sum.

💡 Example

aggregate sum(metric_group('uuid'))

🔗 See also sumIf • sum2 • avg

`• sum2`

Sums the squares of values in expression.

Arguments

Name	Type	Description
`expression`	Number / Timeseries	Values to square and sum.

💡 Example

aggregate sum2(metric_group('uuid'))

🔗 See also sum • stddev • avg

`• sumIf`

Sums values of expression where condition is true.

Arguments

Name	Type	Description
`expression`	Number / Timeseries	Values to sum.
`condition`	Boolean	Filter predicate.

💡 Example

aggregate sumIf(metric_group('uuid'), interval.state.category.name = 'Uptime')

🔗 See also sum • avgIf • countIf

`• coalesce`

Returns the first non‑null of its arguments; otherwise returns the fallback.

Arguments

Name	Type	Description
`expression`	Any	Primary value.
`fallback`	Any	Returned if primary is null (same type).

💡 Example

aggregate coalesce(last(metric_group('uuid')), 0)

🔗 See also first • last • toString

`• count`

Counts the number of unique values of expression.

Arguments

Name	Type	Description
`expression`	Any / Timeseries	Values to deduplicate and count.

💡 Example

aggregate count(interval.run.name)

🔗 See also countIf • sum • set

`• countIf`

Behavior depends on the first argument:

countIf(metric('uuid'), condition) or countIf(metric_group('uuid'), condition) — counts metric data points where condition is true.
countIf(condition) or countIf(interval.run.name, condition) (non-metric first arg) — counts intervals matching the condition.

Arguments

Name	Type	Description
`expression`	Metric or interval (optional)	Metric/interval context for point counting.
`condition`	Boolean	Filter predicate.

💡 Example

aggregate countIf(interval.run.name, interval.state.type = 'Uptime')

🔗 See also count • sumIf • avgIf

`• greatest`

Returns the maximum from a list of constants/expressions.

Arguments

Name	Type	Description
`value1, value2, ...`	Number	Two or more inputs.

💡 Example

aggregate greatest(avg(metric_group('uuid')), 1.23, 0)

🔗 See also least • max • coalesce

`• least`

Returns the minimum from a list of constants/expressions.

Arguments

Name	Type	Description
`value1, value2, ...`	Number	Two or more inputs.

💡 Example

aggregate least(avg(metric_group('uuid')), 1.23, 0)

🔗 See also greatest • min • coalesce

`• if`

Returns then_value if condition is true; otherwise else_value.

Arguments

Name	Type	Description
`condition`	Boolean	Predicate.
`then_value`	Any	Result if true.
`else_value`	Any	Result if false.

💡 Example

aggregate if(avg(metric_group('uuid')) > 1, 'ok', 'low')

🔗 See also multiIf • coalesce • isNull

`• multiIf`

Evaluates pairs of (condition, value) in order; returns the first match, else the final default value.

Arguments

Name	Type	Description
`condition1, value1, ...`	Mixed	One or more condition/value pairs, plus default.

💡 Example

aggregate multiIf(x>10,'high', x>5,'mid', 'low')

🔗 See also if • coalesce • isNull

`• isNaN`

Returns 1 if expression is not-a-number, else 0.

Arguments

Name	Type	Description
`expression`	Number	Numeric input.

💡 Example

aggregate isNaN(avg(metric_group('uuid')))

🔗 See also isNull • isNotNull • if

`• isNull`

Returns 1 if expression is null, else 0.

Arguments

Name	Type	Description
`expression`	Any	Input value.

💡 Example

aggregate isNull(last(metric_group('uuid')))

🔗 See also isNotNull • coalesce • if

`• isNotNull`

Returns 1 if expression is not null, else 0.

Arguments

Name	Type	Description
`expression`	Any	Input value.

💡 Example

aggregate isNotNull(last(metric_group('uuid')))

🔗 See also isNull • coalesce • if

`• isZeroOrNull`

Returns 1 if expression equals 0 or is null; otherwise 0.

Arguments

Name	Type	Description
`expression`	Number / Any	Input value.

💡 Example

aggregate isZeroOrNull(avg(metric_group('uuid')))

🔗 See also isNull • isNotNull • if

`• concat`

Concatenates arguments left‑to‑right into a single string.

Arguments

Name	Type	Description
`value1, value2, ...`	String / Any	Values converted to string and concatenated.

💡 Example

aggregate concat('Product ', toString(metric('uuid')))

🔗 See also formatRow • toString • match

`• containsAny`

Returns true if string contains at least one of the provided values.

Arguments

Name	Type	Description
`string`	String	Source text.
`values`	String list	One or more substrings to search for.

💡 Example

where containsAny(interval.state.comment, 'alarm','reset','fault')

🔗 See also like • match • concat

`• like`

Returns 1 if value matches the LIKE pattern (e.g., %substr%).

Arguments

Name	Type	Description
`value`	String	Source text.
`pattern`	String	LIKE pattern with `%` and `_`.

💡 Example

aggregate like(product.name, '%Widget%')

🔗 See also match • containsAny • concat

`• match`

Returns 1 if value matches the regular expression regex.

Arguments

Name	Type	Description
`value`	String	Source text.
`regex`	String	Regular expression (e.g., `'[0-9]*-03'`).

💡 Example

aggregate match(product.name, '[0-9]*-03')

🔗 See also like • containsAny • concat

`• left`

Returns the leftmost length characters of string.

Arguments

Name	Type	Description
`string`	String	Source text.
`length`	Integer	Number of characters to return.

💡 Example

aggregate left(product.name, 5)

🔗 See also right • substring • concat

`• right`

Returns the rightmost length characters of string.

Arguments

Name	Type	Description
`string`	String	Source text.
`length`	Integer	Number of characters to return.

💡 Example

aggregate right(product.name, 5)

🔗 See also left • substring • concat

`• substring`

Returns a substring of string starting at start for length characters. substr is an alias. If starting position is negative, counts from the end of the string.

Arguments

Name	Type	Description
`string`	String	Source text.
`start`	Integer	Starting position (1-based).
`length`	Integer	Number of characters to return.

💡 Example

aggregate substring(product.name, 2, 4)

🔗 See also left • right • concat

`• length`

Returns the number of characters in string.

Arguments

Name	Type	Description
`string`	String	Source text.

💡 Example

aggregate length(product.name)

🔗 See also left • right • concat

`• jsonExists`

Returns 1 if an object exists at path in object, else 0.

Arguments

Name	Type	Description
`object`	JSON	JSON document.
`path`	String	JSONPath (e.g., `'$.field'`).

💡 Example

aggregate jsonExists(metadata, '$.config.version')

🔗 See also jsonValue • jsonQuery • isNotNull

`• jsonQuery`

Extract any JSON object, array, or sub-structure from a JSON object using a JSONPath. jsonQuery returns any JSON structure at the given path — objects, arrays, lists, or mixed types. If the path matches multiple elements, the function returns an array of matches. Use it to retrieve a complex structure and further process it.

Arguments

Name	Type	Description
`object`	JSON	The JSON document (stringified or native JSON).
`path`	String	JSONPath expression (supports wildcards and array indices).

💡 Example

1. Extract a sub-object

aggregate jsonQuery(metadata, '$.path')

2. Extract several array indices

aggregate jsonQuery(metadata, '$.path[*][0,1,2]')

🔗 See also jsonValue • jsonExists

`• jsonValue`

Extract a single scalar (string, number, boolean) from a JSON object. If the path matches multiple elements, only the first is returned.

Arguments

Name	Type	Description
`object`	JSON/Array	JSON document or array (including output from `jsonQuery`).
`path`	String	JSONPath pointing to a scalar.

💡 Example

1. Extract a scalar

aggregate jsonValue(metadata, '$.path')

2. Extract from a particular array index

aggregate jsonValue(metadata, '$.path[1][0]')

🔗 See also jsonQuery • jsonExists • toString

`• cast`

Casts value to type and returns the converted value.

Arguments

Name	Type	Description
`value`	Any	Value to cast.
`type`	String	Target type (e.g., `'Float64'`).

💡 Example

aggregate cast('1.23','Float64')

🔗 See also castOrNull • toString • coalesce

`• castOrNull`

Attempts to cast value to type; returns null if not possible.

Arguments

Name	Type	Description
`value`	Any	Value to cast.
`type`	String	Target type (e.g., `'Float64'`).

💡 Example

aggregate castOrNull(metadata.field, 'Float64')

🔗 See also cast • toString • coalesce

`• toString`

Converts expression to a string representation.

Arguments

Name	Type	Description
`expression`	Any	Value to stringify.

💡 Example

aggregate toString(metric_group('uuid'))

🔗 See also formatRow • concat • cast

`• all`

Returns an array of every value that expression takes within the range.

Arguments

Name	Type	Description
`expression`	Any / Timeseries	Series or field.

💡 Example

aggregate all(interval.state.type)

🔗 See also count • formatRow • set

`• encodeURLComponent`

Encodes a string for safe use in URLs (percent‑encoding).

Arguments

Name	Type	Description
`string`	String	Text to encode.

💡 Example

aggregate encodeURLComponent(concat('SOP ', toString(query.start)))

🔗 See also concat • toString • formatRow

`• formatRow`

Formats one or more expression values into a single formatted cell.

Arguments

Name	Type	Description
`format`	String	One of `CSV`, `TSV`, `JSONCompactEachRow`.
`expression...`	Any	One or more values to format.

💡 Example

aggregate formatRow('CSV', metric('uuid'), avg(metric_group('uuid')))

🔗 See also concat • toString

`• set`

Returns a set of unique values for expression (interval or metrics).

Arguments

Name	Type	Description
`expression...`	Any / Timeseries	Values to deduplicate.

💡 Example

aggregate set(metric_group('uuid'))

🔗 See also count • all

`• floor`

Rounds value down to the largest number less than or equal to it, optionally to N decimal places.

Arguments

Name	Type	Description
`value`	Number	Input value.
`N`	Integer	Optional decimal places (default 0).

💡 Example

aggregate floor(avg(metric_group('uuid')), 2)

🔗 See also ceil • trunc • round

`• ceil`

Rounds value up to the smallest number greater than or equal to it, optionally to N decimal places. ceiling is an alias.

Arguments

Name	Type	Description
`value`	Number	Input value.
`N`	Integer	Optional decimal places (default 0).

💡 Example

aggregate ceil(avg(metric_group('uuid')), 1)

🔗 See also floor • trunc • round

`• trunc`

Truncates value to the number with the largest absolute value less than or equal to it, optionally to N decimal places. truncate is an alias.

Arguments

Name	Type	Description
`value`	Number	Input value.
`N`	Integer	Optional decimal places (default 0).

💡 Example

aggregate trunc(avg(metric_group('uuid')), 1)

🔗 See also floor • ceil • round

`• sign`

Returns the sign of value: -1 for negative, 0 for zero, 1 for positive.

Arguments

Name	Type	Description
`value`	Number	Input value.

💡 Example

aggregate sign(avg(metric_group('uuid')))

🔗 See also abs • if • greatest

`• log10`

Returns the base-10 logarithm of value.

Arguments

Name	Type	Description
`value`	Number	Positive input value.

💡 Example

aggregate log10(avg(metric_group('uuid')))

🔗 See also log • log2 • exp

`• log2`

Returns the base-2 logarithm of value.

Arguments

Name	Type	Description
`value`	Number	Positive input value.

💡 Example

aggregate log2(avg(metric_group('uuid')))

🔗 See also log • log10 • exp

`• cbrt`

Returns the cube root of value.

Arguments

Name	Type	Description
`value`	Number	Input value.

💡 Example

aggregate cbrt(avg(metric_group('uuid')))

🔗 See also sqrt • pow • abs

`• clamp`

Constrains value to be within the range [min, max].

Arguments

Name	Type	Description
`value`	Number	Input value.
`min`	Number	Minimum bound.
`max`	Number	Maximum bound.

💡 Example

aggregate clamp(avg(metric_group('uuid')), 0, 100)

🔗 See also greatest • least • if

`• isFinite`

Returns 1 if value is finite (not infinite or NaN), else 0.

Arguments

Name	Type	Description
`value`	Number	Input value.

💡 Example

aggregate isFinite(avg(metric_group('uuid')))

🔗 See also isNaN • isNull • if

`• age`

Calculates the difference between enddate and startdate in the specified unit.

Arguments

Name	Type	Description
`unit`	String	One of ‘microsecond’, ‘millisecond’, ‘second’, ‘minute’, ‘hour’, ‘day’, ‘week’, ‘month’, ‘quarter’, ‘year’.
`startdate`	DateTime	Start date/time.
`enddate`	DateTime	End date/time.

💡 Example

aggregate age('hour', query.start, query.end)

🔗 See also addDays • addHours • date_trunc

`• addDays`

Adds num days to datetime.

Arguments

Name	Type	Description
`datetime`	DateTime	Input date/time.
`num`	Integer	Number of days to add (can be negative).

💡 Example

aggregate addDays(query.start, 7)

🔗 See also age • addHours • addMinutes

`• addHours`

Adds num hours to datetime.

Arguments

Name	Type	Description
`datetime`	DateTime	Input date/time.
`num`	Integer	Number of hours to add (can be negative).

💡 Example

aggregate addHours(query.start, 8)

🔗 See also age • addDays • addMinutes

`• addMinutes`

Adds num minutes to datetime.

Arguments

Name	Type	Description
`datetime`	DateTime	Input date/time.
`num`	Integer	Number of minutes to add (can be negative).

💡 Example

aggregate addMinutes(query.start, 30)

🔗 See also age • addDays • addHours

`• formatDateTime`

Formats datetime according to format string (e.g., %Y-%m-%d).

Arguments

Name	Type	Description
`datetime`	DateTime	Input date/time.
`format`	String	Format string, see abbreviations below
`timezone` (optional)	String	Timezone for formatting (e.g., ‘UTC’, ‘America/Los_Angeles’). Defaults to query timezone.

Abbreviation	Description
`%a`	Abbreviated weekday name (Sun..Sat)
`%b`	Abbreviated month name (Jan..Dec)
`%c`	Month, numeric (0..12)
`%D`	Day of the month with English suffix (0th, 1st, 2nd, 3rd, …)
`%d`	Day of the month, numeric (00..31)
`%e`	Day of the month, numeric (0..31)
`%f`	Microseconds (000000..999999)
`%H`	Hour (00..23)
`%h`	Hour (01..12)
`%I`	Hour (01..12)
`%i`	Minutes, numeric (00..59)
`%j`	Day of year (001..366)
`%k`	Hour (0..23)
`%l`	Hour (1..12)
`%M`	Month name (January..December)
`%m`	Month, numeric (00..12)
`%p`	AM or PM
`%r`	Time, 12-hour (hh:mm:ss followed by AM or PM)
`%S`	Seconds (00..59)
`%s`	Seconds (00..59)
`%T`	Time, 24-hour (hh:mm:ss)
`%U`	Week (00..53), where Sunday is the first day of the week; WEEK() mode 0
`%u`	Week (00..53), where Monday is the first day of the week; WEEK() mode 1
`%V`	Week (01..53), where Sunday is the first day of the week; WEEK() mode 2; used with %X
`%v`	Week (01..53), where Monday is the first day of the week; WEEK() mode 3; used with %x
`%W`	Weekday name (Sunday..Saturday)
`%w`	Day of the week (0=Sunday..6=Saturday)
`%X`	Year for the week where Sunday is the first day of the week, numeric, four digits; used with %V
`%x`	Year for the week, where Monday is the first day of the week, numeric, four digits; used with %v
`%Y`	Year, numeric, four digits
`%y`	Year, numeric (two digits)
`%%`	A literal % character

💡 Example

aggregate formatDateTime(toDateTime64('2022-01-01 03:03:03', 3), '%M')

`• toInt32`

Converts value to a 32-bit signed integer, truncating fractional digits.

Arguments

Name	Type	Description
`value`	Any	Value to convert.

💡 Example

aggregate toInt32(avg(metric_group('uuid')))

🔗 See also toInt64 • toFloat64 • cast

`• toInt64`

Converts value to a 64-bit signed integer, truncating fractional digits.

Arguments

Name	Type	Description
`value`	Any	Value to convert.

💡 Example

aggregate toInt64(avg(metric_group('uuid')))

🔗 See also toInt32 • toFloat64 • cast

`• toFloat32`

Converts value to a 32-bit floating point number.

Arguments

Name	Type	Description
`value`	Any	Value to convert.

💡 Example

aggregate toFloat32(metric_group('uuid'))

🔗 See also toFloat64 • toInt32 • cast

`• toFloat64`

Converts value to a 64-bit floating point number.

Arguments

Name	Type	Description
`value`	Any	Value to convert.

💡 Example

aggregate toFloat64(metric_group('uuid'))

🔗 See also toFloat32 • toInt64 • cast

`• toDate`

Converts value to a Date type.

Arguments

Name	Type	Description
`value`	Any	Value to convert (string, datetime, or timestamp).

💡 Example

aggregate toDate(query.start)

🔗 See also toDateTime64 • toUnixTimestamp • cast

`• date_diff`

Returns the difference between two datetimes in the given unit.

Arguments

Name	Type	Description
`unit`	String	One of `s`, `m`, `h`, `D`, `W`, `M`, `Q`, `Y`.
`start`	DateTime	Start timestamp.
`end`	DateTime	End timestamp.

💡 Example

aggregate date_diff('h', query.start, query.end) as query_hours
where time from now()-7D to now()

`• lag` / `• lead`

Window-only aggregates that return the previous (lag) or next (lead) value in a window partition. Use with OVER window_name. Returns 0 when no previous/next row exists.

💡 Example

aggregate lag(metric_group('uuid')) over w as prev_value
where time from now()-1D to now()
window w as (partition by line.id order by timestamp)

🔗 See also Window Functions