Dt Dql Essentials

DQL Essentials Skill

DQL is a pipeline-based query language. Queries chain commands with | to filter, transform, and aggregate data. DQL has unique syntax that differs from SQL — load this skill before writing any DQL query.

______________________________________________________________________

When to Load References

Before working on specific tasks, load the relevant reference:

______________________________________________________________________

DQL Reference Index

Use this index to route from a function group (e.g. time functions, conversions) to its detailed spec, or from a function name to its spec file.

______________________________________________________________________

Syntax Pitfalls

______________________________________________________________________

Fetch Command → Data Model

DQL queries start with fetch <data_object> or timeseries. There is no `fetch dt.metric` — metrics use timeseries.

dt.entity.* is deprecated — use dt.smartscape.* and smartscapeNodes for new queries.

Discover all available data objects: fetch dt.system.data_objects | fields name, display_name, type

→ references/semantic-dictionary.md for full field namespaces

______________________________________________________________________

samplingRatio Parameter

fetch supports a samplingRatio: parameter to reduce the volume of data read — useful for improving query performance on large datasets.

fetch spans, samplingRatio:100   // reads ~1% of data

Allowed values: depend on the concrete data object and range from 1, 10, 100, 1000, 10000 to 100000, the highest level only available for logs and spans.

Sampling is hierarchical for spans, user.events and user.sessions: a record included at a higher ratio (e.g. 100) is guaranteed to also appear at lower ratios (e.g. 10, 1), but not vice versa. This means results at different ratios are subsets of each other. All other non-metric data objects are sampled independently per record, so results at different ratios are not subsets.

The actual ratio applied is accessible via the dt.system.sampling_ratio field. Use it to extrapolate sampled counts back to true totals:

fetch logs, samplingRatio:10
| summarize count_extrapolated = sum(dt.system.sampling_ratio)

______________________________________________________________________

Metric Discovery

To search for available metrics by keyword, use the command metrics:

metrics from: now() - 1h
| filter contains(metric.key, "replay")
| summarize count(), by: {metric.key}
| sort `count()` desc

There is no `fetch dt.metric` or fetch dt.metrics or fetch dt.system.metrics — those data objects do not exist.

______________________________________________________________________

Timeseries Aggregation Functions

The timeseries command supports only these aggregation functions:

Helpers (use alongside an aggregation): start(), end().

Not supported by `timeseries`: countIf, collectArray, stddev, variance, takeAny, takeFirst, takeLast — use summarize or makeTimeseries.

The rollup: parameter

Metrics are pre-aggregated at ingest time. rollup: controls how raw data points are combined per time slot. Required for percentile, median, percentRank — without it the query silently returns no results. avg/min/max/sum/count work without rollup:.

Single aggregation — rollup: at command level. Multiple aggregations in {} — rollup: must go inside each function call (command-level rollup: causes UNKNOWN_PARAMETER_DEFINED):

timeseries p90 = percentile(dt.process.handles.file_descriptors_percent_used, 90), rollup: avg

timeseries {
  p90 = percentile(dt.process.handles.file_descriptors_percent_used, 90, rollup: avg),
  med = median(dt.process.handles.file_descriptors_percent_used, rollup: avg),
  avg_val = avg(dt.process.handles.file_descriptors_percent_used)
}, by: {dt.smartscape.host}

Values: avg (gauges), min, max, sum (counters), total.

Timeseries-to-scalar conversion

There are two ways to collapse a timeseries to a scalar. Prefer the scalar:true parameter when you only need the single aggregated value — it is more efficient because no array is materialized. Fall back to array functions when you need both the full series and a derived scalar in the same query.

Preferred: `scalar:true` on the aggregation function

Pass scalar:true to any timeseries aggregation function. The result field contains a single value instead of an array, and no intermediate array is allocated:

timeseries avg_cpu = avg(dt.host.cpu.usage, scalar:true), by:{dt.smartscape.host}

timeseries {
  avg_cpu = avg(dt.host.cpu.usage, scalar:true),
  max_cpu = max(dt.host.cpu.usage, scalar:true)
}, by:{dt.smartscape.host}

Fallback: array functions in `fieldsAdd`

When you need the full time series array alongside a derived scalar, use array functions in a subsequent | fieldsAdd:

timeseries cpu = avg(dt.host.cpu.usage), by:{dt.smartscape.host}
| fieldsAdd avg_cpu = arrayAvg(cpu), max_cpu = arrayMax(cpu)

______________________________________________________________________

Time Alignment (@-operator)

The @ operator aligns timestamps to a boundary — agents often get this wrong.

Rules:

• Order: offset before alignment — now()-2h@h, not now()@h-2h
• No space between @ and the unit — now()@h not now() @h
• m = minutes, M = months — do not confuse them

→ references/dql/dql-functions-timeseries.md for the full list of timeseries aggregations and rollup: rules → references/dql/dql-functions-array.md for arrayAvg / arrayMax / arrayPercentile / … spec

______________________________________________________________________

Entity & Smartscape Patterns

Entity fields are scoped per type — entity.id does not exist. Use smartscapeNodes for topology queries.

Use toSmartscapeId() for ID conversion from strings (required!).

→ references/smartscape-topology-navigation.md

______________________________________________________________________

makeTimeseries Command

makeTimeseries builds a time-bucketed series from event data (logs, spans, bizevents). Unlike timeseries (which queries pre-ingested metrics), makeTimeseries aggregates data in a pipeline.

Do not pipe `timeseries` directly into `makeTimeseries` — it fails with INVALID_IMPLICIT_TIME_DEFAULT. To re-aggregate metric data, use start() + expand (see references/summarization.md).

fetch logs
| makeTimeseries
    total = count(),
    errors = countIf(loglevel == "ERROR"),
    interval: 5m,
    by: {k8s.cluster.name}
| fieldsAdd error_rate = errors[] * 100.0 / total[]

Key parameters: interval:, by:{}, from:/to:, bins:, time: (timestamp field), spread: (for count/countIf only), nonempty:.

→ references/summarization.md for full makeTimeseries patterns and summarize bucketing → references/iterative-expressions.md for timeseries array manipulation

______________________________________________________________________

matchesValue() Usage

Use matchesValue() for array fields such as dt.tags:

| filter matchesValue(dt.tags, "env:production")

• Not for string fields with special characters — use contains() for those
• matchesValue() on a scalar string field does not behave like a wildcard or fuzzy match

______________________________________________________________________

Chained Lookup Pattern

Each lookup command without a fields parameter removes all existing fields starting with the prefix (default: `lookup.`) before adding new ones. When chaining multiple lookups, use fields parameter or custom prefixes to preserve the result:

Option 1 (default): the desired fields are known.

fetch bizevents
// Step 1: First lookup — enrich orders with product info
| lookup [fetch bizevents
    | filter event.type == "product_catalog"
    | fields product_id, category],
  sourceField: product_id, lookupField: product_id, fields: {product_id, product_category = category}

// Step 2: Second lookup — specify fields with a different name
| lookup [fetch bizevents
    | filter event.type == "warehouse_stock"
    | fields category, warehouse_region],
  sourceField: product_category, lookupField: category, fields: {warehouse_region, warehouse_category = category}

All 4 lookup fields product_id, product_category, warehouse_region, and warehouse_category are available. Without the fields:{...} parameter, the fields would be prefixed with lookup. and the second lookup command would delete the fields added by the first lookup.

Option 2: keep all fields from the lookup.

fetch bizevents
// Step 1: First lookup — enrich orders with product info
| lookup [fetch bizevents
    | filter event.type == "product_catalog"
    | fields product_id, category],
  sourceField: product_id, lookupField: product_id, prefix: "product."

// Step 2: Second lookup — specify fields with a different prefix
| lookup [fetch bizevents
    | filter event.type == "warehouse_stock"
    | fields category, warehouse_region],
  sourceField: product_category, lookupField: category, prefix: "warehouse."

The new fields are: product.product_id, product.category, warehouse.category, warehouse.warehouse_region. All fields starting with product. or warehouse. are removed from the original source. Without the dedicated prefix, both lookup commands would use the same prefix (lookup.) and the second lookup drops the first lookup's results — producing empty fields.

______________________________________________________________________

makeTimeseries Command

makeTimeseries builds a time-bucketed series from event data (logs, spans, bizevents). Unlike timeseries (which queries pre-ingested metrics), makeTimeseries aggregates data in a pipeline.

Do not pipe `timeseries` directly into `makeTimeseries` — it fails with INVALID_IMPLICIT_TIME_DEFAULT. To re-aggregate metric data, use start() + expand (see references/summarization.md).

fetch logs
| makeTimeseries
    {total = count(),
    errors = countIf(loglevel == "ERROR")},
    interval: 5m,
    by: {k8s.cluster.name}
| fieldsAdd error_rate = errors[] * 100.0 / total[]

Key parameters: interval:, by:{}, from:/to:, bins:, time: (timestamp field), spread: (for count/countIf only), nonempty:. → references/dql/dql-commands.md for full spec.

Entity existence timeline using spread::

smartscapeNodes "HOST"
| makeTimeseries concurrently_existing_hosts = count(), spread: lifetime

→ references/iterative-expressions.md for timeseries array manipulation

______________________________________________________________________

Timeframe Specification

Access to data requires specification of a timeframe. It can be specified in the UI, as REST API parameters, or in a DQL query explicitly using a pair of parameters: from: and to: (if one is omitted it defaults to now()), or alternatively using a single timeframe: parameter. Timeframe can be expressed using absolute values or relative expressions vs. current time. The time alignment operator (@) can be used to round timestamps to time unit boundaries — see references/operators.md for full details.

Examples

from:now()-1h@h, to:now()@h     // last complete hour

from:now()-1d@d, to:now()@d     // yesterday complete

from:now()@M                    // this month so far, till now

from:now()-2h@h                 // go back 2 hours, then align to hour boundary

See references/operators.md for the full @ alignment-unit table (including m vs. M, week-day variants w1–w7, and factor rules like @3h).

Absolute timestamps

Use ISO 8601 format:

from:"2024-01-15T08:00:00Z", to:"2024-01-15T09:00:00Z"

______________________________________________________________________

Modifying Time

Key concepts

• DQL has 3 specialized types related to time:
• timestamp — internally kept as number of nanoseconds since epoch, but exposed as date/time in a particular timezone
• timeframe — a pair of 2 timestamps (start and end)
• duration — internally kept as number of nanoseconds, but exposed as duration scaled to a reasonable factor (e.g. ms, minutes, days)

Rules

• Subtracting timestamps yields a duration: timestamp - timestamp → duration
• Duration divided by duration yields a double: e.g. 2h / 1m = 120.0
• Scalar times duration yields a duration: e.g. no_of_h * 1h → duration
• For extraction of time elements (hours, days of month, etc):
• ✅ Use time functions. They support calendar and time zones properly including DST.
• ❌ Avoid using formatTimestamp for extracting time components.
• ❌ Avoid converting timestamps and durations to double/long and using division, modulo, and constants expressing time units as nanoseconds.

References

• [references/useful-expressions.md](references/useful-expressions.md) — Useful expressions in DQL
• [references/semantic-dictionary.md](references/semantic-dictionary.md) — Dynatrace Semantic Dictionary: field namespaces, data models, stability levels, query patterns, and best practices
• [references/summarization.md](references/summarization.md) — Various applications of summarize and makeTimeseries commands
• [references/iterative-expressions.md](references/iterative-expressions.md) — Array and timeseries manipulation (creation, modifications, use in filters) using DQL
• [references/smartscape-topology-navigation.md](references/smartscape-topology-navigation.md) — Smartscape topology navigation syntax and patterns
• [references/optimization.md](references/optimization.md) — DQL query optimization: filter placement, time ranges, field selection, and performance best practices
• [references/operators.md](references/operators.md) — in operator (subquery syntax) and full @ time alignment unit reference

DQL Commands

Param notation: name = required positional · name: = required named · suffix * = variadic · suffix ? = optional · types listed as |-separated names or any (all scalar+collection types)

Table of Contents

`append` · `data` · `dedup` · `describe` · `expand` · `fetch` · `fields` · `fieldsAdd` · `fieldsFlatten` · `fieldsKeep` · `fieldsRemove` · `fieldsRename` · `fieldsSnapshot` · `fieldsSummary` · `filter` · `filterOut` · `join` · `joinNested` · `limit` · `load` · `lookup` · `makeTimeseries` · `metrics` · `parse` · `search` · `smartscapeEdges` · `smartscapeNodes` · `sort` · `summarize` · `timeseries` · `traverse`

append

Merges the current list of records with another list of records. append source source (—) — The sub-query to append.

data

Creates a static dataset to work with. data [json ,] record, … json:? (String) — A JSON string that holds an object or an array of objects that will represent the data set. record* (Record) — One of the static records that should be part of the data set. expression* (any) — An expression to add to the record. [assign:optional]

dedup

Removes duplicates from a list of records. dedup expression, … [, sort: expression [asc|desc], …] expression* (any) — An expression defining the how duplicate entries should be sorted (the first record by this order will be kept). direction:*? (—) — An expression defining the how duplicate entries should be sorted (the first record by this order will be kept). [default:"ascending"] filterPushThrough:? (Boolean) — Whether the filter should be push through the dedup command. [default:FALSE] limit:? (Long) — The maximum number of records returned by the dedup command. [default:9223372036854775807] expression* (any) — An expression for which all different unique values should be kept.

describe

Describes the schema of a given data object. describe dataObject dataObject (—) — The data object to describe.

expand

Expands an array into separate records. expand expression [, limit] expression (Array) — A field or an array expression that should be expanded. [assign:optional] limit:? (Long) — The maximum number of items to expand. [default:2147483647, min:1]

fetch

Loads data from the resource. fetch dataObject [, bucket: name, …] [, from] [, to] [, timeframe] [, samplingRatio] [, scanLimitGBytes] dataObject (—) — The data object to fetch data for. name* (—) — A bucket (name or pattern) to retrieve data from. from:? (Duration|Long|String|Timestamp) — The start of the timeframe (if no explicit timeframe is specified). A duration is interpreted as an offset from now(). [min:0] to:? (Duration|Long|String|Timestamp) — The end of the timeframe (if no explicit timeframe is specified). A duration is interpreted as an offset from now(). [min:0] timeframe:? (String|Timeframe) — The desired timeframe (if not specified, global timeframe is used). samplingRatio:? (Double|Long) — The desired sampling ratio. [min:1] scanLimitGBytes:? (Long) — The maximum number of gigabytes that shall be scanned during loading data.

fields

Keeps only the specified fields. fields expression, … expression* (any) — An expression that will be retained in the result list. [assign:optional]

fieldsAdd

Evaluates an expression and appends or replaces a field. fieldsAdd expression, … expression* (any) — An expression, its result will be added to the record list. [assign:optional]

fieldsFlatten

Adds fields from a record to the current record list. fieldsFlatten expression [, prefix] [, fields: { [field, …] }] [, depth] expression (Record) — An expression returning the record from which to add the fields. prefix:? (—) — Prefix that is applied to all fields that are going to be added. field* (any) — Field to add from the record. [assign:optional] depth:? (Long) — Flatten nested records until the specified depth is reached. [default:1, min:1]

fieldsKeep

Keeps the fields in the result. fieldsKeep field, … field* (any) — A field or fields based on a pattern to keep in the record list.

fieldsRemove

Removes fields from the result. fieldsRemove field, … field* (any) — A field or fields based on a pattern to remove from the record list.

fieldsRename

Renames a field. fieldsRename field, … field* (any) — A field to rename (needs to be fully qualified). [assign:mandatory]

fieldsSnapshot

Loads a fields snapshot for a data source. fieldsSnapshot dataObject [, by: { [field, …] }] [, bucket: name, …] dataObject (—) — The data object for which to load the fields snapshot. field* (any) — A field name from the result schema to group by. name* (—) — A bucket for which to retrieve fields.

fieldsSummary

Calculates the facets for the listed fields. fieldsSummary [topValues] [, extrapolateSamples ,] field, … topValues:? (Long) — The number of top values for each field. [default:20] extrapolateSamples:? (Boolean) — Whether the result should be extrapolated using the sampling rate. [default:FALSE] field* (any) — A field identifier.

filter

Reduces the number of records in a list by excluding all records not matching a specific condition. filter condition condition (Boolean) — The condition all records have to fulfill.

filterOut

Removes records that match a specific condition. filterOut condition condition (Boolean) — The condition all records have to fulfill.

join

Joins all records from the source and the sub-query as long as they fulfill the join condition. join joinTable [, kind] [, executionOrder ,] on: condition, … [, prefix] [, fields: { [field, …] }] joinTable (—) — Sub-query for records with fields to add or overwrite in the input. kind:? (—) — Defines how records get joined. [default:inner] executionOrder:? (—) — Defines which side of the join will be executed first. [default:auto] broadcast:? (—) — Defines broadcasting strategy. [default:enabled] condition* (—) — Records must match this condition in order to be joined. prefix:? (—) — Specifies a prefix string for all new fields. [default:"right."] field* (any) — A field from the sub-query to add to the source. [assign:optional]

joinNested

Joins all records from the source and the sub-query as long as they fulfill the join condition. The matching results from the sub-query are added as an array of nested records. joinNested joinTable, alias, on: condition, … [, executionOrder] [, fields: { [field, …] }] joinTable (—) — Sub-query for records with fields to add or overwrite in the input. [assign:mandatory] condition* (—) — Records must match this condition in order to be joined. executionOrder:? (—) — Defines which side of the join will be executed first. [default:auto] broadcast:? (—) — Defines broadcasting strategy. [default:enabled] field* (any) — A field from the sub-query to add to the source. [assign:optional]

limit

Limits the number of returned records. limit size size (Long) — The maximum number of records. [min:0]

load

Load command to read tabular file stored by Save command. load tabularFile [, offset] tabularFile (—) — The name of the tabular file that was saved. offset:? (Long) — Number of skipped records. [min:0]

lookup

Loads an external record and adds the fields to the current record. lookup lookupTable [, sourceField ,] lookupField [, prefix] [, fields: { [field, …] }] [, executionOrder] lookupTable (—) — Sub-query for records with fields to add or overwrite in the input. sourceField:? (any) — The field to use from the source for equality comparison. lookupField: (any) — The field to use from the sub-query for equality comparison. prefix:? (—) — Specifies a prefix string for all new fields. [default:"lookup."] field* (any) — A field from the sub-query to add to the source. [assign:optional] executionOrder:? (—) — Defines which side of the join will be executed first. [default:auto] broadcast:? (—) — Defines broadcasting strategy. [default:enabled]

makeTimeseries

Converts the input into the time series format. makeTimeseries [by: { [expression, …] }] [, interval] [, bins] [, from] [, to] [, timeframe] [, time] [, spread] [, nonempty ,] aggregation, … expression* (any) — An expression to split the series by. [assign:optional] interval:? (Duration) — An expression that provides the duration of a bins in the series. bins:? (Long) — An positive non-zero long integer number that defines the number of bins that shall be created within the series timeframe. [default:120, min:0] from:? (Duration|Timestamp) — The global timeframe start for the series for which values should be considered. to:? (Duration|Timestamp) — The global timeframe end for the series for which values should be considered. timeframe:? (Timeframe) — The global timeframe end for the series for which values should be considered. time:? (Timestamp) — A timestamp expression that provides the timestamp for the bucket calculation of the values in the series. spread:? (Timeframe) — A timeframe expression that provides the timeframe for the bucket calculation of the values in the series. nonempty:? (Boolean) — Produces empty series when there is no data. [default:FALSE] aggregation* (—) — The series that shall be calculated. [assign:optional] default:*? (Double|Long) — The default value in the series bin, if no value is present. rate:*? (Duration) — The rate the resulting series values shall be scaled to.

metrics

Loads metric data. metrics [[bucket: name, …] [, from] [, to] [, timeframe]] name* (—) — A bucket (name or pattern) to retrieve data from. from:? (Duration|String|Timestamp) — The global timeframe start for retrieving metrics. to:? (Duration|String|Timestamp) — The global timeframe end for retrieving metrics. timeframe:? (String|Timeframe) — The global timeframe for retrieving metrics.

parse

Parses a record field and puts the result(s) into one or more fields as specified in the pattern. parse expression, pattern [, preserveFieldsOnFailure] [, parsingPrerequisite] expression (String) — A field or string expression to parse. pattern (—) — The parse pattern. preserveFieldsOnFailure:? (Boolean) — Determines if fields values should be preserved if parsing fails. [default:FALSE] parsingPrerequisite:? (Boolean) — Determines if record should be parsed. [default:TRUE] baseTime:? (Timestamp) — A timestamp expression providing the base time for date/time parsing.

search

Reduces the number of records in a list by excluding all records where the search condition doesn't apply. search condition condition (—) — The condition all records have to fulfill. caseSensitive:? (Boolean) — Whether search patterns should be considered as case-sensitive (default: false). [default:FALSE] scope:? (—) — Where search patterns should be searched. [default:"all"] field* (any) — A field on which to apply search patterns. field* (any) — A field to be excluded from the search for search patterns.

smartscapeEdges

Returns the edges of a smartscape graph. smartscapeEdges [from] [, to] [, timeframe ,] type, … from:? (Duration|String|Timestamp) — The global timeframe start for retrieving the smartscape edges. to:? (Duration|String|Timestamp) — The global timeframe end for retrieving the smartscape edges. timeframe:? (String|Timeframe) — The global timeframe for retrieving the smartscape edges. type* (—) — The type or type pattern of the smartscape edges.

smartscapeNodes

Returns the nodes of a smartscape graph. smartscapeNodes [from] [, to] [, timeframe ,] type, … from:? (Duration|String|Timestamp) — The global timeframe start for retrieving the smartscape edges. to:? (Duration|String|Timestamp) — The global timeframe end for retrieving the smartscape edges. timeframe:? (String|Timeframe) — The global timeframe for retrieving the smartscape edges. type* (—) — The type or type pattern of the smartscape nodes.

sort

Sorts the records. sort expression [asc|desc], … expression* (any) — An expression defining the sort order. direction:*? (—) — The direction of the sorting. [default:"ascending"]

summarize

Groups together records that have the same values for a given field and aggregates them. summarize aggregation, … [, by: { [expression, …] }] expression* (any) — An expression to group by. [assign:optional] aggregation* (any) — An aggregation function (min, max, avg, ...). [assign:optional]

timeseries

Reads metrics in the time series format from the data source. timeseries [bucket: name, …] [, from] [, to] [, timeframe] [, by: { [expression, …] }] [, filter] [, interval] [, bins] [, shift] [, nonempty] [, union ,] metric, … name* (—) — A bucket (name or pattern) to retrieve data from. from:? (Duration|String|Timestamp) — The global timeframe start for the series for which values should be considered. to:? (Duration|String|Timestamp) — The global timeframe end for the series for which values should be considered. timeframe:? (String|Timeframe) — The global timeframe for the series for which values should be considered. expression* (any) — An expression to split the series by. [assign:optional] filter:? (Boolean) — An additional filter condition that shall be applied on the source records before time-/space-aggregation. interval:? (Duration) — A suggested interval for the series. bins:? (Long) — A suggested number of bins in the series. [default:120, min:0] shift:? (Duration) — Shifts the effective timeframe by the provided duration. nonempty:? (Boolean) — Produces empty series when there is no data. [default:FALSE] union:? (Boolean) — Whether the results will be combined as union if multiple metric keys are specified. [default:FALSE] metric* (—) — The metric that shall be calculated. [assign:optional] rollup:*? (—) — The rollup type that shall be used for the metric. default:*? (Double|Long) — The default value to fill gaps. rate:*? (Duration) — The rate the resulting series values shall be scaled to.

traverse

Switches from the current list of records to a different one. traverse edgeType, …, targetType, … [, direction] [, fieldsKeep: { [field, …] }] [, nodeId] edgeType* (—) — The type of the edge to traverse. targetType* (—) — The type of the target nodes. direction:? (—) — The traversal direction. [default:"forward"] field* (any) — A field or field pattern to keep in the traversal history. nodeId:? (SmartscapeId) — The field that contains the id of the node in the incoming records. [default:id]

DQL Data Types

DQL Functions — Aggregation

Param notation: name = required positional · name: = required named · suffix * = variadic · suffix ? = optional · types listed as |-separated names or any (all scalar+collection types)

Table of Contents

`avg` · `collectArray` · `collectDistinct` · `correlation` · `count` · `countDistinct` · `countDistinctApprox` · `countDistinctExact` · `countIf` · `max` · `median` · `min` · `percentRank` · `percentile` · `percentileFromSamples` · `percentiles` · `stddev` · `sum` · `takeAny` · `takeFirst` · `takeLast` · `takeMax` · `takeMin` · `variance`

avg

Calculates the average value of a field for a list of records. avg(expression) expression (Double|Duration|Long) — The expression from which to compute the average. → Double|Duration

collectArray

Collects the values of the provided field into an array (preservation of order not guaranteed). collectArray(expression [, maxLength] [, expand]) expression (any) — The expression from which to collect the values. maxLength:? (Long) — The maximum length of the resulting array. [min:0] expand:? (Boolean) — The boolean expression that indicates whether the output should be a flat array. [default:FALSE] → Array

collectDistinct

Collects the values of the provided field into an array (preservation of order not guaranteed). collectDistinct(expression [, maxLength] [, expand]) expression (any) — The expression from which to collect the distinct values. maxLength:? (Long) — The maximum length of the resulting array. [min:0] expand:? (Boolean) — The boolean expression that indicates whether the output should be a flat array. [default:FALSE] → Array

correlation

Calculates the correlation of two fields for a list of records. correlation(expression1, expression2) expression1 (Double|Long) — The first expression to correlate. expression2 (Double|Long) — The second expression to correlate. → Double

count

Counts the total number of records. count() → Long

countDistinct

Calculates the cardinality of unique values of a field for a list of records based on a stochastic estimation. countDistinct(expression [, precision]) expression (any) — The expression from which to count distinct elements. precision:? (Long) — The precision in the interval [3, 16]. [default:14, min:3] → Long

countDistinctApprox

Calculates the cardinality of unique values of a field for a list of records based on a stochastic estimation. countDistinctApprox(expression [, precision]) expression (any) — The expression from which to count distinct elements. precision:? (Long) — The precision in the interval [3, 16]. [default:14, min:3] → Long

countDistinctExact

Calculates the cardinality of unique values of a field for a list of records. countDistinctExact(expression) expression (any) — The expression from which to count distinct elements. → Long

countIf

Counts the number of records that match the condition. countIf(condition) condition (Boolean) — The expression from which to count matched elements. → Long

max

Calculates the maximum value of a field for a list of records. max(expression) expression (Boolean|Double|Duration|Long|String|Timestamp) — The expression from which to get the maximum element. → Boolean|Double|Duration|Long|String|Timestamp

median

Calculates the median value of a field for a list of records. median(expression [, weight]) expression (Boolean|Double|Duration|Long|Timestamp) — The expression from which to compute the median. weight:? (Double|Long) — The weight of the corresponding expression (e.g. its sampling ratio). [default:1, min:0] → Boolean|Double|Duration|Timestamp

min

Calculates the minimum value of a field for a list of records. min(expression) expression (Boolean|Double|Duration|Long|String|Timestamp) — The expression from which to get the minimum element. → Boolean|Double|Duration|Long|String|Timestamp

percentRank

Calculates the percentile rank for a given value. percentRank(expression, value) expression (Boolean|Double|Duration|Long|Timestamp) — The expression for which to compute a percentile rank. value (Boolean|Double|Duration|Long|Timestamp) — The value for which to retrieve the percentile. → Double

percentile

Calculates the percentile value of a field for a list of records:percentile(x, 50) == median(x). percentile(expression, percentile [, weight]) expression (Boolean|Double|Duration|Long|Timestamp) — The expression from which to compute a percentile. percentile (Double|Long) — The percentile to compute, between 0 and 100. [min:0] weight:? (Double|Long) — The weight of the corresponding expression (e.g. its sampling ratio). [default:1, min:0] → Boolean|Double|Duration|Timestamp

percentileFromSamples

Calculates the percentile value of array fields. percentileFromSamples(expression, percentile [, originalCount]) expression (Array) — The array expression from which to compute a percentile. percentile (Double|Long) — The percentile to compute, between 0 and 100. [min:0] originalCount:? (Double|Long) — The original element count of the given array expression. [min:0] → Boolean|Double|Duration|Timestamp

percentiles

Calculates multiple percentile values of a field for a list of records (similar to percentile, but returns an array of values instead of a single one). percentiles(expression [, weight ,] percentile, …) expression (Boolean|Double|Duration|Long|Timestamp) — The expression from which to compute a percentile. weight:? (Double|Long) — The weight of the corresponding expression (e.g. its sampling ratio). [default:1, min:0] percentile* (Double|Long) — The percentile to compute, between 0 and 100. [min:0] → Array

stddev

Calculates the standard deviation of a field for a list of records. stddev(expression) expression (Double|Long) — The expression from which to compute standard deviation. → Double

sum

Calculates the sum of a field for a list of records. sum(expression) expression (Double|Duration|Long) — The expression from which to compute the sum. → Double|Duration

takeAny

Returns a value of a field for a list of records. Any record can be given despite records are ordered or not. takeAny(expression) expression (any) — The expression from which to take any element. → any

takeFirst

Returns the first value of a field for a list of records in the current order. takeFirst(expression) expression (any) — The expression from which to take the first element. → any

takeLast

Returns the last value of a field for a list of records in the current order. takeLast(expression) expression (any) — The expression from which to take the last element. → any

takeMax

Returns the maximum value of a field for a list of records. The records will be ordered based on the field data type and the field value and the maximum will be taken. takeMax(expression) expression (any) — The expression from which to take the maximum element. → any

takeMin

Returns the minimum value of a field for a list of records. The records will be ordered based on the field data type and the field value and the minimum will be taken. takeMin(expression) expression (any) — The expression from which to take the minimum element. → any

variance

Calculates the variance of a field for a list of records. variance(expression) expression (Double|Long) — The expression from which to compute variance. → Double

DQL Functions — Array

Param notation: name = required positional · name: = required named · suffix * = variadic · suffix ? = optional · types listed as |-separated names or any (all scalar+collection types)

Table of Contents

`arrayAvg` · `arrayConcat` · `arrayCumulativeSum` · `arrayDelta` · `arrayDiff` · `arrayDistinct` · `arrayFirst` · `arrayFlatten` · `arrayIndexOf` · `arrayLast` · `arrayLastIndexOf` · `arrayMax` · `arrayMedian` · `arrayMin` · `arrayMovingAvg` · `arrayMovingMax` · `arrayMovingMin` · `arrayMovingSum` · `arrayPercentile` · `arrayRemoveNulls` · `arrayReverse` · `arraySize` · `arraySlice` · `arraySort` · `arraySum` · `arrayToString` · `vectorCosineDistance` · `vectorInnerProductDistance` · `vectorL1Distance` · `vectorL2Distance`

_array function_

arrayAvg

Returns the average of an array. Values that are not numeric are ignored. 0 if there is no matching element. arrayAvg(array) array (Array) — an array expression → Double

arrayConcat

Concatenates multiple arrays into a single array. arrayConcat(array, …) array* (Array) — Array expression that should be combined with others. → Array

arrayCumulativeSum

Returns the sums of elements from the input array and all elements with a lower index. arrayCumulativeSum(array) array (Array) — an array expression → Array

arrayDelta

Returns array of delta of array elements arrayDelta(array) array (Array) — an array expression → Array

arrayDiff

Returns array of same length where result[i] == input[i] - input[i-1]. arrayDiff(array) array (Array) — an array expression → Array

arrayDistinct

Returns the array without duplicates. arrayDistinct(array) array (Array) — an array expression → Array

arrayFirst

Returns the first non-null element of an array (use myArray[0] to get the first nullable element). arrayFirst(array) array (Array) — an array expression → any

arrayFlatten

Returns flattened array arrayFlatten(array) array (Array) — an array expression → Array

arrayIndexOf

Returns the index of the first array element with the given value. arrayIndexOf(array, value) array (Array) — The array expression in which the value is searched for. value (any) — The primitive value to search for in the expression. → Long

arrayLast

Returns the last non-null element of an array (use myArray[-1] to get the last nullable element). arrayLast(array) array (Array) — an array expression → any

arrayLastIndexOf

Returns the index of the last array element with the given value. arrayLastIndexOf(array, value) array (Array) — The array expression in which the value is searched for. value (any) — The primitive value to search for in the expression. → Long

arrayMax

Returns the maximum (biggest) number of an array. Values that are not numeric are ignored. null if there is no matching element. arrayMax(array) array (Array) — an array expression → any

arrayMedian

Returns the median of the members of an array. arrayMedian(expression) expression (Array) — The array from which to compute the median. → Boolean|Double|Duration|Timestamp

arrayMin

Returns the minimum (smallest) number of an array. Values that are not numeric are ignored. null if there is no matching element. arrayMin(array) array (Array) — an array expression → any

arrayMovingAvg

Returns the averages of elements from the input array calculated according to the moving window size. arrayMovingAvg(array, windowSize) array (Array) — The array of numeric values. windowSize (Long) — The size of moving window. [min:0] → Array

arrayMovingMax

Returns the maximums of elements from the input array calculated according to the moving window size. arrayMovingMax(array, windowSize) array (Array) — The array of numeric values. windowSize (Long) — The size of moving window. [min:0] → Array

arrayMovingMin

Returns the minimums of elements from the input array calculated according to the moving window size. arrayMovingMin(array, windowSize) array (Array) — The array of numeric values. windowSize (Long) — The size of moving window. [min:0] → Array

arrayMovingSum

Returns the sums of elements from the input array calculated according to the moving window size. arrayMovingSum(array, windowSize) array (Array) — The array of numeric values. windowSize (Long) — The size of moving window. [min:0] → Array

arrayPercentile

Returns a percentile of the members of an array. arrayPercentile(expression, percentile) expression (Array) — The array from which to compute a percentile. percentile (Double|Long) — The percentile to compute, between 0 and 100. [min:0] → Boolean|Double|Duration|Timestamp

arrayRemoveNulls

Returns the array where NULL elements are removed. arrayRemoveNulls(array) array (Array) — an array expression → Array

arrayReverse

Returns the array with elements in reversed order. arrayReverse(array) array (Array) — an array expression → Array

arraySize

Returns the size of an array. arraySize(array) array (Array) — an array expression → Long

arraySlice

Returns a slice of an array. arraySlice(array [, from] [, to]) array (Array) — an array expression from:? (Long) — Index of first element to include in the resulting array, inclusive, relative to start of array if positive, relative to end if negative. Clamped at array bounds. [default:0] to:? (Long) — Index of last element to include in the resulting array, exclusive, relative to start of array if positive, relative to end if negative. Clamped at array bounds. [default:9223372036854775807] → Array

arraySort

Returns the array with members sorted in ascending order. arraySort(array [, direction]) array (Array) — an array expression direction:? (—) — direction [default:"ascending"] → Array

arraySum

Returns the sum of an array. Values that are not numeric are ignored. 0 if there is no matching element. arraySum(array) array (Array) — an array expression → Double

arrayToString

Converts an array to a string. arrayToString(array [, delimiter]) array (Array) — Array expression that should be converted to a string. delimiter:? (String) — A constant string expression that is added between the concatenated array elements. [default:""] → String

vectorCosineDistance

Calculates the cosine distance between two arrays. vectorCosineDistance(firstExpression, secondExpression) firstExpression (Array) — An array of numeric values. secondExpression (Array) — An array of numeric values. → Double

vectorInnerProductDistance

Calculates the inner product distance between two arrays. vectorInnerProductDistance(firstExpression, secondExpression) firstExpression (Array) — An array of numeric values. secondExpression (Array) — An array of numeric values. → Double

vectorL1Distance

Calculates the L1 distance between two arrays. vectorL1Distance(firstExpression, secondExpression) firstExpression (Array) — An array of numeric values. secondExpression (Array) — An array of numeric values. → Double

vectorL2Distance

Calculates the L2 distance between two arrays. vectorL2Distance(firstExpression, secondExpression) firstExpression (Array) — An array of numeric values. secondExpression (Array) — An array of numeric values. → Double

DQL Functions — Bitwise

Param notation: name = required positional · name: = required named · suffix * = variadic · suffix ? = optional · types listed as |-separated names or any (all scalar+collection types)

_bitwise function_

bitwiseAnd

Calculates the bitwise and between two long expressions. bitwiseAnd(firstExpression, secondExpression) firstExpression (Long) — The first long expression for the binary bitwise operation. secondExpression (Long) — The second long expression for the binary bitwise operation. → Long

bitwiseCountOnes

Counts the bits set to one of a long expression. bitwiseCountOnes(expression) expression (Long) — The long expression whose bits set to one will be counted. → Long

bitwiseNot

Inverts the bits of a long expression. bitwiseNot(expression) expression (Long) — The long expression whose bits will be inverted. → Long

bitwiseOr

Calculates the bitwise or between two long expressions. bitwiseOr(firstExpression, secondExpression) firstExpression (Long) — The first long expression for the binary bitwise operation. secondExpression (Long) — The second long expression for the binary bitwise operation. → Long

bitwiseShiftLeft

Bitwise left shift long expression by a number of given bits. bitwiseShiftLeft(expression, numberOfBits) expression (Long) — The long expression that will be bitwise shifted left. numberOfBits (Long) — The number of bits by which the expression will be shifted left. → Long

bitwiseShiftRight

Bitwise right shift long expression by a number of given bits. bitwiseShiftRight(expression, numberOfBits [, ignoreSign]) expression (Long) — The long expression that will be bitwise shifted right. numberOfBits (Long) — The number of bits by which the expression will be shifted right. ignoreSign:? (Boolean) — The boolean expression that indicates if the sign bit should be ignored (treated like any bit) while shifting. If false, the sign bit is preserved and just the other bits are shifted. [default:FALSE] → Long

bitwiseXor

Calculates the bitwise xor between two long expressions. bitwiseXor(firstExpression, secondExpression) firstExpression (Long) — The first long expression for the binary bitwise operation. secondExpression (Long) — The second long expression for the binary bitwise operation. → Long

DQL Functions — Boolean

Param notation: name = required positional · name: = required named · suffix * = variadic · suffix ? = optional · types listed as |-separated names or any (all scalar+collection types)

_boolean checks_

exists

Tests if a field exists. exists(field) field (any) — The name of the field that will be checked if it exists. → Boolean

in

Tests if a needle value is contained in any of the haystack parameters. in(needle, haystack, …) needle (any) — The element(s) to search for (the needle). haystack* (any) — The elements where to search for the needle element (the haystack). → Boolean

isFalseOrNull

Tests if a value is false or null isFalseOrNull(expression) expression (Boolean) — The expression to check if it is false or null. → Boolean

isNotNull

Tests if a value is not null isNotNull(expression) expression (any) — The expression to check if it is not null. → Boolean

isNull

Tests if a value is null. isNull(expression) expression (any) — The expression to check if it is null. → Boolean

isTrueOrNull

Tests if a value is true or null. isTrueOrNull(expression) expression (Boolean) — The expression to check if it is true or null. → Boolean

isUid128

Tests if a uid value is of subtype uid128. isUid128(expression) expression (UID) — The uid expression that will be checked if it is of subtype uid128. → Boolean

isUid64

Tests if a uid value is of subtype uid64. isUid64(expression) expression (UID) — The uid expression that will be checked if it is of subtype uid64. → Boolean

isUuid

Tests if a uid value is of subtype uuid. isUuid(expression) expression (UID) — The uid expression that will be checked if it is of subtype uuid. → Boolean

DQL Functions — Cast

Param notation: name = required positional · name: = required named · suffix * = variadic · suffix ? = optional · types listed as |-separated names or any (all scalar+collection types)

_cast function_

asArray

Returns array value if the value is array, otherwise null. asArray(value) value (Array) — The expression to cast as an array. → Array

asBinary

Returns binary value (byte array) if the value is binary, otherwise null. asBinary(value) value (Binary) — The expression to cast as a byte array. → Binary

asBoolean

Returns boolean value if the value is boolean, otherwise null. asBoolean(value) value (Boolean) — The expression to cast as a boolean. → Boolean

asDouble

Returns double value if the value is double, otherwise null. asDouble(value) value (Double) — The expression to cast as a double. → Double

asDuration

Returns duration value if the value is duration, otherwise null. asDuration(value) value (Duration) — The expression to cast as a duration. → Duration

asIp

Returns ip_address value if the value is ip_address, otherwise null. asIp(value) value (IpAddress) — The expression to cast as an ip address. → IpAddress

asLong

Returns long value if the value is long, otherwise null. asLong(value) value (Long) — The expression to cast as a long. → Long

asNumber

Returns same value if the value is integer, long, double, otherwise null. asNumber(value) value (Double|Long) — The expression to cast as a number. → Double|Long

asRecord

Returns record value if the value is record, otherwise null. asRecord(value) value (Record) — The expression to cast as a record. → Record

asSmartscapeId

Returns smartscapeId value if the value is smartscapeId, otherwise null. asSmartscapeId(value) value (SmartscapeId) — The expression to cast as a smartscape id. → SmartscapeId

asString

Returns string value if the value is string, otherwise null. asString(value) value (String) — The expression to cast as a string. → String

asTimeframe

Returns timeframe value if the value is timeframe, otherwise null. asTimeframe(value) value (Timeframe) — The expression to cast as a timeframe. → Timeframe

asTimestamp

Returns timestamp value if the value is timestamp, otherwise null. asTimestamp(value) value (Timestamp) — The expression to cast as a timestamp. → Timestamp

asUid

Returns uid value if the value is uid, otherwise null. asUid(value) value (UID) — The expression to cast as a uid. → UID

DQL Functions — Constant

Param notation: name = required positional · name: = required named · suffix * = variadic · suffix ? = optional · types listed as |-separated names or any (all scalar+collection types)

_mathematical constant_

e

Returns Euler’s number. e() → Double

pi

Returns the constant value of PI (Archimedes’ number). pi() → Double

DQL Functions — Conversion

Param notation: name = required positional · name: = required named · suffix * = variadic · suffix ? = optional · types listed as |-separated names or any (all scalar+collection types)

_conversion function_

toArray

Returns the value if it is an array. Otherwise, converts a value to the single element array holding that value. toArray(value) value (any) — The expression to convert to an array if possible. → Array

toBoolean

Converts a value to boolean if the value is of a suitable type. If the argument is an array, the element at position 0 is converted. toBoolean(value) value (Array|Boolean|Double|Long|String) — The expression to convert to a boolean if possible. → Boolean

toDouble

Converts a value to double if the value is of a suitable type. If the argument is an array, the element at position 0 is converted. toDouble(value) value (Array|Boolean|Double|Duration|IpAddress|Long|String|Timestamp|UID) — The expression to convert to a double if possible. → Double

toDuration

Converts a value to duration if the value is of a suitable type. If the argument is an array, the element at position 0 is converted. toDuration(value) value (Array|Double|Duration|Long|String|Timeframe) — The expression to convert to a duration if possible. → Duration

toIp

Converts a value to ip_address if the value is of a suitable type. If the argument is an array, the element at position 0 is converted. toIp(value) value (Array|Double|IpAddress|Long|String) — The expression to convert to an ip address if possible. → IpAddress

toLong

Converts a value to long if the value is of a suitable type. If the argument is an array, the element at position 0 is converted. toLong(value) value (Array|Boolean|Double|Duration|IpAddress|Long|String|Timestamp|UID) — The expression to convert to a long if possible. → Long

toSmartscapeId

Converts a value to smartscapeId if the value is of a suitable type. If the argument is an array, the element at position 0 is converted. toSmartscapeId(value) value (Array|SmartscapeId|String) — The expression to convert to a smartscape id if possible. → SmartscapeId

toString

Returns the string representation of a value. toString(value) value (any) — The expression to convert to a string if possible. → String

toTimeframe

Converts a value to timeframe if the value is of a suitable type. If the argument is an array, the element at position 0 is converted. toTimeframe(value) value (Array|String|Timeframe) — The expression to convert to a timeframe if possible. → Timeframe

toTimestamp

Converts a value to timestamp if the value is of a suitable type. If the argument is an array, the element at position 0 is converted. toTimestamp(value) value (Array|Double|Long|String|Timestamp) — The expression to convert to a timestamp if possible. → Timestamp

toUid

Converts a value to uid if the value is of a suitable type. If the argument is an array, the element at position 0 is converted. toUid(value) value (Array|Double|Long|String|UID) — The expression to convert to a uid if possible. → UID

toVariant (deprecated)

Converts a value to variant with boxed element inside. toVariant(value) value (any) — The expression to convert to a variant if possible. → any

DQL Functions — Create

Param notation: name = required positional · name: = required named · suffix * = variadic · suffix ? = optional · types listed as |-separated names or any (all scalar+collection types)

_create function for primitive data types_

array

Creates an array from the list of given parameters. array(expression, …) expression* (any) — An element inside the array. → Array

duration

Creates a duration from the given amount and time unit. duration(value, unit) value (Double|Long) — The numeric value for the duration. unit (String) — The time unit of the duration. → Duration

ip

Creates an ip from the given string expression. ip(expression) expression (String) — The string expression for an ip address → IpAddress

record

Creates a record from the keys and values of the parameters. record(expression, …) expression* (any) — An expression to add to the record. [assign:optional] → Record

smartscapeId

Creates a smartscapeId from the given string and long expression. smartscapeId(type, numericId) type (String) — The type of smartscapeId as string. numericId (Long) — The numeric id of smartscapeId as long. → SmartscapeId

timeframe

Creates a timeframe from the given start and end timestamp or duration. timeframe(from [, to]) from (Duration|String|Timestamp) — The start of the timeframe. Can be a timestamp or a duration. A duration is interpreted as an offset from now(). to:? (Duration|String|Timestamp) — The end of the timeframe. Can be a timestamp or a duration. A duration is interpreted as an offset from now(). [default:now()] → Timeframe

timestamp

Creates a timestamp from the provided values. timestamp(year, month, day, hour, minute, second [, millis] [, micros] [, nanos] [, timezone]) year (Long) — The year of the timestamp as a number. month (Long) — The month of the timestamp as a number. day (Long) — The day of the timestamp as a number. hour (Long) — The hour of the timestamp as a number. minute (Long) — The minute of the timestamp as a number. second (Long) — The second of the timestamp as a number. millis:? (Long) — The millisecond of the timestamp as a number. [default:0] micros:? (Long) — The microsecond of the timestamp as a number. [default:0] nanos:? (Long) — The nanosecond of the timestamp as a number. [default:0] timezone:? (—) — The timezone used to format the timestamp. → Timestamp

timestampFromUnixMillis

Creates a timestamp from the given milliseconds since Unix epoch. timestampFromUnixMillis(millis) millis (Long) — Milliseconds since unix start time. → Timestamp

timestampFromUnixNanos

Creates a timestamp from the given nanoseconds since Unix epoch. timestampFromUnixNanos(nanos) nanos (Long) — Nanoseconds since unix start time. → Timestamp

timestampFromUnixSeconds

Creates a timestamp from the given seconds since Unix epoch. timestampFromUnixSeconds(seconds) seconds (Long) — Seconds since unix start time. → Timestamp

uid128

Creates a uid from the given two long expressions. uid128(firstExpression, secondExpression) firstExpression (Long) — The 1st long expression for a uid. secondExpression (Long) — The 2nd long expression for a uid. → UID

uid64

Creates a uid from the given long expression. uid64(expression) expression (Long) — The long expression for a uid. → UID

uuid

Creates a uuid from the given two long expressions. uuid(mostSignificantBits, leastSignificantBits) mostSignificantBits (Long) — The 1st long expression for the most significant bits of a uuid. leastSignificantBits (Long) — The 2nd long expression for the least significant bits of a uuid. → UID

DQL Functions — Cryptographic

Param notation: name = required positional · name: = required named · suffix * = variadic · suffix ? = optional · types listed as |-separated names or any (all scalar+collection types)

_cryptographic string function_

hashCrc32

Returns a CRC32 hash for the given expression. hashCrc32(expression) expression (Binary|String) — The string expression that will be hashed. → String

hashMd5

Returns a MD5 hash for the given expression. hashMd5(expression) expression (Binary|String) — The string expression that will be hashed. → String

hashSha1

Returns a SHA-1 hash for the given expression. hashSha1(expression) expression (Binary|String) — The string expression that will be hashed. → String

hashSha256

Returns a SHA-256 hash for the given expression. hashSha256(expression) expression (Binary|String) — The string expression that will be hashed. → String

hashSha512

Returns a SHA-512 hash for the given expression. hashSha512(expression) expression (Binary|String) — The string expression that will be hashed. → String

hashXxHash32

Returns a xxHash32 hash for the given expression. hashXxHash32(expression) expression (Binary|String) — The expression that is considered for the hash function. → String

hashXxHash64

Returns a xxHash64 hash for the given expression. hashXxHash64(expression) expression (Binary|String) — The expression that is considered for the hash function. → String

DQL Functions — Entities

Param notation: name = required positional · name: = required named · suffix * = variadic · suffix ? = optional · types listed as |-separated names or any (all scalar+collection types)

_entities function_

classicEntitySelector

Returns entities matching the specified entity selector. classicEntitySelector(entitySelector) entitySelector (String) — The entity selector string. → Array

entityAttr

Returns the attribute value for an entity. entityAttr(expression, name [, type]) expression (any) — The expression to determine the entity ID. name (—) — The entity attribute name that to be queried. type:? (—) — The entity type that to be queried. → any

entityName

Returns the name of an entity. entityName(expression [, type]) expression (any) — The expression to determine the entity ID. type:? (—) — The entity type that to be queried. → String

DQL Functions — Time series aggregation for expressions

Param notation: name = required positional · name: = required named · suffix * = variadic · suffix ? = optional · types listed as |-separated names or any (all scalar+collection types)

Table of Contents

`avg` · `count` · `countDistinct` · `countDistinctApprox` · `countDistinctExact` · `countIf` · `end` · `max` · `median` · `min` · `percentRank` · `percentile` · `percentileFromSamples` · `start` · `sum`

_makeTimeseries_

avg

Calculates the average of the expression values in each bucket. avg(expression [, default] [, rate] [, scalar]) expression (Double|Duration|Long) — The expression the aggregation function shall be applied to. default:? (Double|Long) — The default value to fill gaps. [default:NULL] rate:? (Duration) — The rate the series values shall be scaled to. scalar:? (Boolean) — Flag to indicate that a single scalar value spanning the whole timeframe shall be calculated. [default:FALSE] → Array|Double

count

Counts the number of records in each bucket. count([[default] [, rate] [, scalar]]) default:? (Double|Long) — The default value to fill gaps. [default:NULL] rate:? (Duration) — The rate the series values shall be scaled to. scalar:? (Boolean) — Flag to indicate that a single scalar value spanning the whole timeframe shall be calculated. [default:FALSE] → Array|Double

countDistinct

This function is an alias for countDistinctApprox(). countDistinct(expression [, precision] [, default] [, rate] [, scalar]) expression (any) — The expression the aggregation function shall be applied to. precision:? (Long) — The precision in the interval [3, 16]. [default:14, min:3] default:? (Double|Long) — The default value to fill gaps. [default:NULL] rate:? (Duration) — The rate the series values shall be scaled to. scalar:? (Boolean) — Flag to indicate that a single scalar value spanning the whole timeframe shall be calculated. [default:FALSE] → Array|Double

countDistinctApprox

Counts the approximate number of distinct records in each bucket. countDistinctApprox(expression [, precision] [, default] [, rate] [, scalar]) expression (any) — The expression the aggregation function shall be applied to. precision:? (Long) — The precision in the interval [3, 16]. [default:14, min:3] default:? (Double|Long) — The default value to fill gaps. [default:NULL] rate:? (Duration) — The rate the series values shall be scaled to. scalar:? (Boolean) — Flag to indicate that a single scalar value spanning the whole timeframe shall be calculated. [default:FALSE] → Array|Double

countDistinctExact

Counts the precise number of distinct records in each bucket. countDistinctExact(expression [, default] [, rate] [, scalar]) expression (any) — The expression the aggregation function shall be applied to. default:? (Double|Long) — The default value to fill gaps. [default:NULL] rate:? (Duration) — The rate the series values shall be scaled to. scalar:? (Boolean) — Flag to indicate that a single scalar value spanning the whole timeframe shall be calculated. [default:FALSE] → Array|Double

countIf

Counts the number of records matching the provided condition in each bucket. countIf(expression [, default] [, rate] [, scalar]) expression (Boolean) — The expression the aggregation function shall be applied to. default:? (Double|Long) — The default value to fill gaps. [default:NULL] rate:? (Duration) — The rate the series values shall be scaled to. scalar:? (Boolean) — Flag to indicate that a single scalar value spanning the whole timeframe shall be calculated. [default:FALSE] → Array|Double

end

Produces an array of timestamps representing the end of the bin. end() → Array

max

Calculates the maximum of the expression values in each bucket. max(expression [, default] [, rate] [, scalar]) expression (Double|Duration|Long) — The expression the aggregation function shall be applied to. default:? (Double|Long) — The default value to fill gaps. [default:NULL] rate:? (Duration) — The rate the series values shall be scaled to. scalar:? (Boolean) — Flag to indicate that a single scalar value spanning the whole timeframe shall be calculated. [default:FALSE] → Array|Double

median

Calculates the median of the expression value in each bucket. median(expression [, weight] [, default] [, rate] [, scalar]) expression (Double|Duration|Long) — The expression the aggregation function shall be applied to. weight:? (Double|Long) — The weight of the corresponding expression (e.g. its sampling ratio). [default:1, min:0] default:? (Double|Long) — The default value to fill gaps. [default:NULL] rate:? (Duration) — The rate the series values shall be scaled to. scalar:? (Boolean) — Flag to indicate that a single scalar value spanning the whole timeframe shall be calculated. [default:FALSE] → Array|Double

min

Calculates the minimum of the expression values in each bucket. min(expression [, default] [, rate] [, scalar]) expression (Double|Duration|Long) — The expression the aggregation function shall be applied to. default:? (Double|Long) — The default value to fill gaps. [default:NULL] rate:? (Duration) — The rate the series values shall be scaled to. scalar:? (Boolean) — Flag to indicate that a single scalar value spanning the whole timeframe shall be calculated. [default:FALSE] → Array|Double

percentRank

Calculates the percentile rank for a given value. percentRank(expression, value [, default] [, rate] [, scalar]) expression (Double|Duration|Long) — The expression the aggregation function shall be applied to. value (Double|Long) — The percentile to compute, between 0 and 100. default:? (Double|Long) — The default value to fill gaps. [default:NULL] rate:? (Duration) — The rate the series values shall be scaled to. scalar:? (Boolean) — Flag to indicate that a single scalar value spanning the whole timeframe shall be calculated. [default:FALSE] → Array|Double

percentile

Calculates the requested percentile of the expression value in each bucket. percentile(expression, percentile [, weight] [, default] [, rate] [, scalar]) expression (Double|Duration|Long) — The expression the aggregation function shall be applied to. percentile (Double|Long) — The percentile to compute, between 0 and 100. [min:0] weight:? (Double|Long) — The weight of the corresponding expression (e.g. its sampling ratio). [default:1, min:0] default:? (Double|Long) — The default value to fill gaps. [default:NULL] rate:? (Duration) — The rate the series values shall be scaled to. scalar:? (Boolean) — Flag to indicate that a single scalar value spanning the whole timeframe shall be calculated. [default:FALSE] → Array|Double

percentileFromSamples

Calculates the requested percentile of the array expression in each bucket. percentileFromSamples(expression, percentile [, originalCount] [, default] [, rate] [, scalar]) expression (Array) — The expression the aggregation function shall be applied to. percentile (Double|Long) — The percentile to compute, between 0 and 100. [min:0] originalCount:? (Double|Long) — The original element count of the given array expression. [min:0] default:? (Double|Long) — The default value to fill gaps. [default:NULL] rate:? (Duration) — The rate the series values shall be scaled to. scalar:? (Boolean) — Flag to indicate that a single scalar value spanning the whole timeframe shall be calculated. [default:FALSE] → Array|Double

start

Produces an array of timestamps representing the start of the bin. start() → Array

sum

Calculates the sum of the expression values in each bucket. sum(expression [, default] [, rate] [, scalar]) expression (Double|Duration|Long) — The expression the aggregation function shall be applied to. default:? (Double|Long) — The default value to fill gaps. [default:NULL] rate:? (Duration) — The rate the series values shall be scaled to. scalar:? (Boolean) — Flag to indicate that a single scalar value spanning the whole timeframe shall be calculated. [default:FALSE] → Array|Double

DQL Functions — Flow

Param notation: name = required positional · name: = required named · suffix * = variadic · suffix ? = optional · types listed as |-separated names or any (all scalar+collection types)

_boolean flow control_

coalesce

Returns the first non-null argument, if any, otherwise null. coalesce(expression, …) expression* (any) — Returned if previous arguments are null. → any

if

Evaluates the condition, and returns the value of either the then or else parameter, depending on whether the condition evaluated to true (then) or false or null (else - or null if the else parameter is missing). if(condition, then [, else]) condition (Boolean) — The condition to check. then (any) — The expression if the condition is true. else:? (any) — The expression if the condition is false or null. [default:NULL] → any

DQL Functions — General

Param notation: name = required positional · name: = required named · suffix * = variadic · suffix ? = optional · types listed as |-separated names or any (all scalar+collection types)

_function_

jsonField

Parses a JSON string and extracts one field. jsonField(expression, fieldName [, seek]) expression (String) — The json string that should be parsed. fieldName (String) — The string literal with the name of the field to be extracted. seek:? (Boolean) — Flag indicating if the function should search for JSON object in the expression. [default:FALSE] → Array|Boolean|Double|Long|Record|String

jsonPath

Parses a JSON string and extracts one field described by a path. jsonPath(expression, jsonPath [, seek]) expression (String) — The json string that should be parsed. jsonPath (—) — The string literal with the JSON-path to be extracted. seek:? (Boolean) — Flag indicating if the function should search for JSON object in the expression. [default:FALSE] → Array|Boolean|Double|Long|Record|String

lookup

Returns a record containing all lookup fields. lookup(lookupTable [, sourceField ,] lookupField [, executionOrder]) lookupTable (—) — Sub-query for records with fields to add or overwrite in the input. sourceField:? (any) — Specifies a field of the source ("left"). lookupField: (any) — Specifies a field of the lookup ("right"). executionOrder:? (—) — Defines which side of the join will be executed first. [default:auto] broadcast:? (—) — Defines broadcasting strategy. [default:enabled] → Record

parse

Extracts a single value from a string as specified in the pattern or a record if there are multiple named matchers. parse(expression, pattern) expression (String) — A field or string expression to parse. pattern (—) — The parse pattern. baseTime:? (Timestamp) — A timestamp expression providing the base time for date/time parsing. → any

parseAll

Extracts several values from a string as specified in the pattern. parseAll(expression, pattern) expression (String) — A field or string expression to parse. pattern (—) — The parse pattern. baseTime:? (Timestamp) — A timestamp expression providing the base time for date/time parsing. → Array

type

Returns the type of a value as string. type(expression [, withSubtype]) expression (any) — The expression to get the type of. withSubtype:? (Boolean) — Whether the type string should include subtype information if available. [default:FALSE] → String

DQL Functions — Get

Param notation: name = required positional · name: = required named · suffix * = variadic · suffix ? = optional · types listed as |-separated names or any (all scalar+collection types)

_get function_

arrayElement

Extracts a single element from an array. arrayElement(expression, index) expression (Array) — The array from which to extract an element. index (Long) — The index of the element to extract. → any

getEnd

Extracts the end timestamp from a timeframe. getEnd(timeframe) timeframe (Timeframe) — The timeframe expression from which to get the end of the interval. → Timestamp

getHighBits

Extracts the most significant bits of a given UID or IP. getHighBits(expression) expression (IpAddress|UID) — The expression from which to extract the most significant bits. → Long

getLowBits

Extracts the least significant bits of a given UID or IP. getLowBits(expression) expression (IpAddress|UID) — The expression from which to extract the least significant bits. → Long

getStart

Extracts the start timestamp from a timeframe. getStart(timeframe) timeframe (Timeframe) — The timeframe expression from which to get the start of the interval. → Timestamp

DQL Functions — Iterative

Param notation: name = required positional · name: = required named · suffix * = variadic · suffix ? = optional · types listed as |-separated names or any (all scalar+collection types)

_iterative function_

iAny

Checks an iterative boolean expression and returns true if it was true at least once, false if not. iAny(expression) expression (Boolean) — The iterative boolean expression. → Boolean

iCollectArray

Collects the results of an iterative expression into an array. iCollectArray(expression) expression (any) — The iterative expression that should be collected into an array. → Array

iIndex

Returns the current index of an iterative expression. iIndex() → Long

DQL Functions — Mathematical

Param notation: name = required positional · name: = required named · suffix * = variadic · suffix ? = optional · types listed as |-separated names or any (all scalar+collection types)

Table of Contents

`abs` · `acos` · `asin` · `atan` · `atan2` · `bin` · `cbrt` · `ceil` · `cos` · `cosh` · `degreeToRadian` · `exp` · `floor` · `hexStringToNumber` · `hypotenuse` · `log` · `log10` · `log1p` · `numberToHexString` · `power` · `radianToDegree` · `random` · `range` · `round` · `signum` · `sin` · `sinh` · `sqrt` · `tan` · `tanh`

_mathematical function_

abs

Returns the absolute value of a numeric expression. abs(expression) expression (Double|Duration|Long) — The numeric expression for which to calculate the absolute value. → Double|Duration|Long

acos

Calculate the acos of the given expression as an angle in radians. acos(expression) expression (Double|Long) — The numeric expression, angle in radians for which to calculate the acos. → Double

asin

Calculate the asin of the given expression as an angle in radians. asin(expression) expression (Double|Long) — The numeric expression, angle in radians for which to calculate the asin. → Double

atan

Calculate the atan of the given expression as an angle in radians. atan(expression) expression (Double|Long) — The numeric expression, angle in radians for which to calculate the atan. → Double

atan2

Calculate the atan2 of the given coordinates. atan2(ordinate, abscissa) ordinate (Double|Long) — The ordinate coordinate. abscissa (Double|Long) — The abscissa coordinate. → Double

bin

Aligns the value of the numeric or timestamp into buckets of the given interval starting at 0 (numeric) or Unix epoch (timestamp). bin(expression, interval [, at]) expression (Double|Duration|Long|Timestamp) — The expression that should be aligned. interval (Double|Duration|Long) — The interval by which to align the expression. at:? (Double|Duration|Long|Timestamp) — The offset to which each interval shall be shifted. [default:NULL] → Double|Duration|Long|Timestamp

cbrt

Computes the real cubic root of a numeric expression cbrt(expression) expression (Double|Long) — The numeric expression for which to calculate the real cubic root. → Double

ceil

Returns the smallest integer greater than or equal to the given number. ceil(expression) expression (Double|Long) — The numeric expression to be rounded up. → Double|Long

cos

Calculate the cos of the given expression as an angle in radians. cos(expression) expression (Double|Long) — The numeric expression, angle in radians for which to calculate the cos. → Double

cosh

Calculate the cosh of the given expression as an angle in radians. cosh(expression) expression (Double|Long) — The numeric expression, angle in radians for which to calculate the cosh. → Double

degreeToRadian

Converts an angle measured in degrees to an approximately equivalent angle measured in radians. degreeToRadian(expression) expression (Double|Long) — The angle to be converted from degrees to radians. → Double

exp

Computes the exponential function of a numeric expression. exp(expression) expression (Double|Long) — The numeric expression for which to calculate the exponential function. → Double

floor

Returns the largest integer smaller than or equal to the given number. floor(expression) expression (Double|Long) — The numeric expression to be rounded down. → Double|Long

hexStringToNumber

Converts a hexadecimal string into a number. hexStringToNumber(expression) expression (String) — The string expression that will be converted to a number. → Double|Long

hypotenuse

Calculate the hypotenuse of the right triangle of given sides. hypotenuse(x, y) x (Double|Long) — Length of the first of the catheti. y (Double|Long) — Length of the second of the catheti. → Double

log

Computes the natural logarithm (base e) of a numeric expression log(expression) expression (Double|Long) — The numeric expression for which to calculate the natural logarithm (base e). → Double

log10

Computes the decadic logarithm (base 10) of a numeric expression. log10(expression) expression (Double|Long) — The numeric expression for which to calculate the decadic logarithm (base 10). → Double

log1p

Computes log(1 + x) of a numeric expression x, where log is the natural logarithm (base e). log1p(expression) expression (Double|Long) — The numeric expression for which to add one and calculate the natural logarithm (base e). → Double

numberToHexString

Converts a number into a hexadecimal string. numberToHexString(expression [, minLength]) expression (Long) — The numeric expression that will be converted to a hexadecimal string. minLength:? (Long) — The minimum length of the returned hexadecimal string. [min:0] → String

power

Raises a base numeric expression to a given exponent. power(base, exponent) base (Double|Long) — The numeric expression acting as the base of the power calculation. exponent (Double|Long) — The numeric expression acting as the exponent of the power calculation. → Double

radianToDegree

Converts an angle measured in radians to an approximately equivalent angle measured in degrees. radianToDegree(expression) expression (Double|Long) — The angle to be converted from radians to degrees. → Double

random

Creates a random double value. random() → Double

range

Aligns the value of the numeric or timestamp into buckets of the given interval starting at 0 (numeric) or Unix epoch (timestamp) keeping start and end of each interval. range(expression, interval [, at]) expression (Double|Duration|Long|Timestamp) — The expression that should be aligned. interval (Double|Duration|Long) — The interval by which to align the expression. at:? (Double|Duration|Long|Timestamp) — The offset to which each interval shall be shifted. [default:NULL] → Record

round

Round the numeric expression to the next long or to the double closest to the provided number of places after the decimal point. round(expression [, decimals]) expression (Double|Long) — Numeric expression to be rounded. decimals:? (Long) — Number of places after the decimal point. [default:0, min:0] → Double|Long

signum

Returns the signum of a numeric expression, that is, 1 if the expression is positive, -1 if it is negative, or 0 if it is zero. signum(expression) expression (Double|Long) — The numeric expression for which to calculate the signum. → Double|Long

sin

Calculate the sin of the given expression as an angle in radians. sin(expression) expression (Double|Long) — The numeric expression, angle in radians for which to calculate the sin. → Double

sinh

Calculate the sinh of the given expression as an angle in radians. sinh(expression) expression (Double|Long) — The numeric expression, angle in radians for which to calculate the sinh. → Double

sqrt

Computes the positive square root of a numeric expression. sqrt(expression) expression (Double|Long) — The numeric expression for which to calculate the square root. → Double

tan

Calculate the tan of the given expression as an angle in radians. tan(expression) expression (Double|Long) — The numeric expression, angle in radians for which to calculate the tan. → Double

tanh

Calculate the tanh of the given expression as an angle in radians. tanh(expression) expression (Double|Long) — The numeric expression, angle in radians for which to calculate the tanh. → Double

DQL Functions — Network

Param notation: name = required positional · name: = required named · suffix * = variadic · suffix ? = optional · types listed as |-separated names or any (all scalar+collection types)

_network function_

ipIn

Checks if an ip address matches with given ip addresses. Returns true if it does, false otherwise. ipIn(needle, haystack, …) needle (Array|IpAddress|String) — The expression that will be compared with the given ip addresses haystack* (Array|IpAddress|String) — The ip addresses with which the expression should be compared → Boolean

ipIsLinkLocal

Checks if a string or ip address expression is a link local ip address. Returns true if it is, false otherwise. ipIsLinkLocal(expression) expression (IpAddress|String) — The string or ip address expression that will be checked. → Boolean

ipIsLoopback

Checks if a string or ip address expression is a loopback ip address. Returns true if it is, false otherwise. ipIsLoopback(expression) expression (IpAddress|String) — The string or ip address expression that will be checked. → Boolean

ipIsPrivate

Checks if a string or ip address expression is a private ip address. Returns true if it is, false otherwise. ipIsPrivate(expression) expression (IpAddress|String) — The string or ip address expression that will be checked. → Boolean

ipIsPublic

Checks if a string or ip address expression is a public ip address. Returns true if it is, false otherwise. ipIsPublic(expression) expression (IpAddress|String) — The string or ip address expression that will be checked. → Boolean

ipMask

Returns an ip address where a given mask is applied ipMask(expression, maskBits [, ipv6MaskBits]) expression (IpAddress|String) — The string or ip address expression that will be masked. maskBits (Long) — The mask bits that should be applied to an ip address. [min:0] ipv6MaskBits:? (Long) — The mask bits that should be applied to an ipv6 address. [min:0] → IpAddress

isIp

Checks if a string or ip address expression is an ip address. Returns true if it is, false otherwise. isIp(expression) expression (IpAddress|String) — The string or ip address expression that will be checked. → Boolean

isIpV4

Checks if a string or ip address expression is an ipv4 address. Returns true if it is, false otherwise. isIpV4(expression) expression (IpAddress|String) — The string or ip address expression that will be checked. → Boolean

isIpV6

Checks if a string or ip address expression is an ipv6 address. Returns true if it is, false otherwise. isIpV6(expression) expression (IpAddress|String) — The string or ip address expression that will be checked. → Boolean

DQL Functions — Smartscape

Param notation: name = required positional · name: = required named · suffix * = variadic · suffix ? = optional · types listed as |-separated names or any (all scalar+collection types)

_smartscape function_

getNodeField

Returns the field value for a smartscape node. getNodeField(expression, name) expression (SmartscapeId|String) — The expression to determine the smartscape node ID. name (String) — The smartscape field name to be queried. → any

getNodeName

Returns the name of a smartscape node. getNodeName(expression) expression (SmartscapeId|String) — The expression to determine the smartscape node ID. → String

DQL Functions — String

Param notation: name = required positional · name: = required named · suffix * = variadic · suffix ? = optional · types listed as |-separated names or any (all scalar+collection types)

Table of Contents

`concat` · `contains` · `decodeBase16ToBinary` · `decodeBase16ToString` · `decodeBase64ToBinary` · `decodeBase64ToString` · `decodeUrl` · `encodeBase16` · `encodeBase64` · `encodeUrl` · `endsWith` · `escape` · `getCharacter` · `indexOf` · `lastIndexOf` · `levenshteinDistance` · `like` · `lower` · `matchesPattern` · `matchesPhrase` · `matchesRegex` · `matchesValue` · `punctuation` · `replacePattern` · `replaceString` · `splitByPattern` · `splitString` · `startsWith` · `stringLength` · `substring` · `trim` · `unescape` · `unescapeHtml` · `upper`

_string function_

concat

Concatenates the expressions into a single string. concat([delimiter ,] expression, …) delimiter:? (String) — A constant string expression that is added between the concatenated expressions. [default:""] expression* (Double|Long|String) — A numeric or string expressions that should be concatenated with others. → String

contains

Searches the string expression for a substring. Returns true if the substring was found, false otherwise. contains(expression, substring [, caseSensitive]) expression (String) — The field or expression to check (the haystack). substring (String) — The substring that should be contained (the needle). caseSensitive:? (Boolean) — caseSensitive [default:TRUE] → Boolean

decodeBase16ToBinary

Decodes the given BASE16-string to a binary. decodeBase16ToBinary(expression) expression (Binary|String) — The encoded string or binary that shall be decoded. → Binary

decodeBase16ToString

Decodes the given BASE16-string to a string. decodeBase16ToString(expression) expression (Binary|String) — The encoded string or binary that shall be decoded. → String

decodeBase64ToBinary

Decodes the given BASE64-string to a binary. decodeBase64ToBinary(expression) expression (Binary|String) — The encoded string or binary that shall be decoded. → Binary

decodeBase64ToString

Decodes the given BASE64-string to a string. decodeBase64ToString(expression) expression (Binary|String) — The encoded string or binary that shall be decoded. → String

decodeUrl

Returns a decoded url string. decodeUrl(expression) expression (String) — The string expression that will be decoded. → String

encodeBase16

Encodes the given binary or string as BASE16-string. encodeBase16(expression) expression (Binary|String) — The string or binary expression that shall be encoded. → String

encodeBase64

Encodes the given binary or string as BASE64-string. encodeBase64(expression) expression (Binary|String) — The string or binary expression that shall be encoded. → String

encodeUrl

Returns an encoded url string. encodeUrl(expression) expression (String) — The string expression that will be encoded. → String

endsWith

Checks if a string expression ends with a suffix. Returns true if does, false otherwise. endsWith(expression, suffix [, caseSensitive]) expression (String) — The string expression that will be checked. suffix (String) — The suffix string with which the expression should end. caseSensitive:? (Boolean) — Whether the check should be done in a case-sensitive way. [default:TRUE] → Boolean

escape

Returns an escaped string. escape(expression) expression (String) — The string expression that will be escaped. → String

getCharacter

Returns the character at a given position from a string expression. Negative positions are counted from the end of the string. getCharacter(expression, position) expression (String) — The string expression from which to get the character. position (Long) — The position at which to get the character (negative positions are counted from the end of the string). → String

indexOf

Finds the index of the first occurrence of a substring in a string expression, starting a forward search from a given index. Returns -1, if the substring is not found. indexOf(expression, substring [, from]) expression (String) — The string expression in which the substring is searched for. substring (String) — The substring expression to search for in the expression. from:? (Long) — The index from which to start the forward search for the first occurrence of the substring within the expression. Negative values are counted from the end of the string. [default:0] → Long

lastIndexOf

Finds the index of the last occurrence of a substring in a string expression, starting a backward search from a given index. Returns -1, if the substring is not found. lastIndexOf(expression, substring [, from]) expression (String) — The string expression in which the substring is searched for. substring (String) — The substring expression to search for in the expression. from:? (Long) — The index from which to start the backward search for the last occurrence of the substring within the expression. Negative values are counted from the end of the string. [default:9223372036854775807] → Long

levenshteinDistance

Computes Levenshtein distance between two given strings. levenshteinDistance(firstExpression, secondExpression) firstExpression (String) — The first string expression to compute the Levenshtein distance from. secondExpression (String) — The second string expression to compute the Levenshtein distance from. → Long

like

Tests if a string expression matches a pattern. If the pattern doesn't contain percent signs then like() acts as == operator (equality check). A percent character in the pattern (%) matches any sequence of zero or more characters. An underscore in the pattern (_) matches a single character. like(expression, pattern) expression (String) — The string expression that will be checked. pattern (String) — The matching pattern. → Boolean

lower

Converts a string to lowercase. lower(expression) expression (String) — The string expression to convert to lowercase. → String

matchesPattern

Tests if a string expression matches the DPL pattern. matchesPattern(expression, pattern) expression (String) — A field or string expression to test. pattern (—) — The matching pattern. → Boolean

matchesPhrase

Matches a phrase against the input string expression using token matchers. matchesPhrase(expression, phrase [, caseSensitive]) expression (Array|String) — The expression (string or array of strings) that should be checked. phrase (String) — The phrase to search for. caseSensitive:? (Boolean) — Whether the match should be done case-sensitive (default: false). [default:FALSE] wildcard:? (String) — A single character that will be used as wildcard (default: ""). [default:""] → Boolean

matchesRegex (deprecated)

Tests if a string expression matches a regular expression. matchesRegex(expression, pattern) expression (String) — The string to check. pattern (String) — The applied regular expression pattern (has to match the whole string). → Boolean

matchesValue

Matches a value against the input expression using token matchers. matchesValue([caseSensitive ,] expression, value, …) caseSensitive:? (Boolean) — Whether the match should be done case-sensitive (default: false). [default:FALSE] wildcard:? (String) — A single character that will be used as wildcard (default: ""). [default:""] expression (Array|SmartscapeId|String) — The expression (string or array of strings) that should be checked. value* (Array|String) — The value to search for using patterns (supports an array of patterns or a list of patterns). → Boolean

punctuation

Returns punctuation characters contained in given string. punctuation(expression [, count] [, withSpace]) expression (String) — The string expression of which to extract the punctuation characters. count:? (Long) — The maximum number of returned punctuation characters. [default:32, min:0] withSpace:? (Boolean) — Whether space characters should be included. [default:FALSE] → String

replacePattern

Replaces each substring of a string that matches the DPL pattern with the given string. replacePattern(expression, pattern, replacement) expression (String) — A field or string expression to replace. pattern (—) — The replacing pattern. replacement (String) — The string that should replace the found substrings. → String

replaceString

Replaces each substring of a string with a given string. replaceString(expression, substring, replacement) expression (String) — The field or expression where substrings should be replaced. substring (String) — The substring that should be replaced. replacement (String) — The string that should replace the found substrings. → String

splitByPattern

Splits a string into an array at each occurrence of the DPL pattern. splitByPattern(expression, pattern) expression (String) — A field or string expression to split. pattern (—) — The splitting pattern. → Array

splitString

Splits a string at each occurrence of a pattern. If not found, returns an array with a single element that contains the full string. Splits into single-byte strings if the pattern is empty. splitString(expression, pattern) expression (String) — The string expression to split up into an array. pattern (String) — The pattern to split the string expression at, or the empty string to split into one-byte strings. → Array

startsWith

Checks if a string expression starts with a prefix. Returns true if does, false otherwise. startsWith(expression, prefix [, caseSensitive]) expression (String) — The string expression that will be checked. prefix (String) — The prefix string with which the expression should start. caseSensitive:? (Boolean) — Whether the check should be done in a case-sensitive way. [default:TRUE] → Boolean

stringLength

Returns number of UTF-16 code units in given string. stringLength(expression) expression (String) — The string expression to get the number of UTF-16 code units for. → Long

substring

Gets part of a string using a start index (inclusive) and an optional end index (exclusive).Negative indexes are relative to the last code unit. Indexes that are out-of-bounds are clamped at the string length for positive indexes, and at zero for negative indexes.Returns empty string in case of out-of-bounds indexes.Indexes are in UTF-16 code units and may not correspond to a single character. substring(expression [, from] [, to]) expression (String) — The string expression to get a substring of. from:? (Long) — Index of first code unit to include in sub-string, inclusive, relative to start of expression if positive, relative to end if negative. Clamped at string bounds. [default:0] to:? (Long) — Index of last code unit to include in sub-string, exclusive, relative to start of expression if positive, relative to end if negative. Clamped at string bounds. [default:9223372036854775807] → String

trim

Returns given string without leading and trailing white-space. trim(expression) expression (String) — The string expression to remove leading and trailing white-space from. → String

unescape

Returns an unescaped string. unescape(expression) expression (String) — The string expression that will be unescaped. → String

unescapeHtml

Returns an unescaped html string. unescapeHtml(expression) expression (String) — The string expression that will be unescaped. → String

upper

Converts a string to uppercase. upper(expression) expression (String) — The string expression to convert to uppercase. → String

DQL Functions — Time

Param notation: name = required positional · name: = required named · suffix * = variadic · suffix ? = optional · types listed as |-separated names or any (all scalar+collection types)

Table of Contents

`formatTimestamp` · `getDayOfMonth` · `getDayOfWeek` · `getDayOfYear` · `getHour` · `getMinute` · `getMonth` · `getSecond` · `getWeekOfYear` · `getYear` · `now` · `unixMillisFromTimestamp` · `unixNanosFromTimestamp` · `unixSecondsFromTimestamp`

_time function_

formatTimestamp

Formats the timestamp according to a format string (using the defined interval). formatTimestamp(timestamp [, interval] [, format] [, timezone] [, locale]) timestamp (Timestamp) — The timestamp expression that should be formatted. interval:? (Duration) — The duration expression used to align the timestamp. format:? (String) — The formatting pattern. [default:"yyyy-MM-dd'T'HH:mm:ss.SSSSSSSSS"] timezone:? (—) — The timezone used to format the timestamp. locale:? (—) — The locale used to format the timestamp. → String

getDayOfMonth

Extracts the day of month from a timestamp. getDayOfMonth(timestamp [, timezone]) timestamp (Timestamp) — The timestamp expression from which the day of month will be extracted. timezone:? (—) — The timezone that should be used. → Long

getDayOfWeek

Extracts the day of week from a timestamp. getDayOfWeek(timestamp [, timezone]) timestamp (Timestamp) — The timestamp expression from which the day of week will be extracted. timezone:? (—) — The timezone that should be used. → Long

getDayOfYear

Extracts the day of year from a timestamp. getDayOfYear(timestamp [, timezone]) timestamp (Timestamp) — The timestamp expression from which the day of year will be extracted. timezone:? (—) — The timezone that should be used. → Long

getHour

Extracts the hour from a timestamp. getHour(timestamp [, timezone]) timestamp (Timestamp) — The timestamp expression from which the hour will be extracted. timezone:? (—) — The timezone that should be used. → Long

getMinute

Extracts the minute from a timestamp. getMinute(timestamp [, timezone]) timestamp (Timestamp) — The timestamp expression from which the minute will be extracted. timezone:? (—) — The timezone that should be used. → Long

getMonth

Extracts the month from a timestamp. getMonth(timestamp [, timezone]) timestamp (Timestamp) — The timestamp expression from which the month will be extracted. timezone:? (—) — The timezone that should be used. → Long

getSecond

Extracts the second from a timestamp. getSecond(timestamp [, timezone]) timestamp (Timestamp) — The timestamp expression from which the second will be extracted. timezone:? (—) — The timezone that should be used. → Long

getWeekOfYear

Extracts the week of year from a timestamp. getWeekOfYear(timestamp [, timezone]) timestamp (Timestamp) — The timestamp expression from which the week of year will be extracted. timezone:? (—) — The timezone that should be used. → Long

getYear

Extracts the year from a timestamp. getYear(timestamp [, timezone]) timestamp (Timestamp) — The timestamp expression from which the year will be extracted. timezone:? (—) — The timezone that should be used. → Long

now

Returns the current time as fixed timestamp of the query start. now() → Timestamp

unixMillisFromTimestamp

Converts a timestamp into milliseconds unixMillisFromTimestamp(timestamp) timestamp (Timestamp) — The timestamp expression which will be converted to milliseconds since epoch. → Long

unixNanosFromTimestamp

Converts a timestamp into nanoseconds unixNanosFromTimestamp(timestamp) timestamp (Timestamp) — The timestamp expression which will be converted to nanoseconds since epoch. → Long

unixSecondsFromTimestamp

Converts a timestamp into seconds unixSecondsFromTimestamp(timestamp) timestamp (Timestamp) — The timestamp expression which will be converted to seconds since epoch. → Long

DQL Parameter Value Types

switch/case or case/when syntax in DQL

DQL does not have built-in syntax like this. As an alternative, a chain of if/else statements can be used.

| fieldsAdd bucket = if(dim == 0,        "0",
else: if(dim <= 3,         "1–3",
else: if(dim <= 18,        "4–18",
else: if(dim <= 32,        "19–32",
else: if(dim >= 100,       "100+",
else:                      "33–99")))))

To avoid having to close many ) at the end, coalesce is useful:

| fieldsAdd bucket = coalesce(
  if(dim == 0,   "0"),
  if(dim <= 3,   "1–3"),
  if(dim <= 18,  "4–18"),
  if(dim <= 32,  "19–32"),
  if(dim >= 100, "100+"),
                 "33–99")