Function guide
The function table lists every OpenSearch PPL scalar function name and how LynxDB executes it. This page explains the shared rules and shows the common functions by category. Aggregations are in the aggregation guide.
How a function name resolves
The PPL scalar catalog (pkg/ppl/functions.go, pkg/ppl/relevance.go) maps
each name to one of three things:
- an IR registry function, often of the same name (
abs,concat,date_format) or an alias (ceilingisceil,substrissubstring,current_timestampisnow); - a lowering onto IR operators (
ifandnullifbecomeCASE,isnullbecomes a NULL test,addbecomes+,matchbecomes a field search); - an unsupported error with its reason (
geoip,multi_match,query_string,simple_query_string,match_bool_prefix). These fail withUNSUPPORTED_FUNCTION.
A name that is not in the catalog fails with UNSUPPORTED_FUNCTION too
(unknown function x). An aggregation used outside an aggregating command is
a SEMANTIC_ERROR.
Shared rules
- NULL. Most functions are strict: a NULL argument gives a NULL result
(
concat('a', null)is NULL). The NULL-aware functions handle NULL arguments themselves:coalesce, the NULL tests,concat_ws(which skips them),tostring,json,json_valid,json_array,array,forall,existsandmvappend. - Types. Arguments are checked against the registry's parameter types at
compile time; a mismatch with a known type is a
TYPE_ERROR(lower(5)), a value of typeanyis converted or rejected per row.concat,concat_wsandmvjoinconvert non-string arguments to strings. - Constants. Some parameters must be constants, such as the patterns of
regexp_matchandlike. They are compiled once per query, and an invalid pattern fails the query before it reads data. - Errors at run time. Invalid values (an unparsable date string, a log of a negative number) give NULL; they do not fail the query.
- Folding. Calls of deterministic functions with constant arguments are
evaluated once at planning time.
now()and the other current-time functions return the query's reference time, the same for every row.
Math
eval a = abs(-3), b = round(3.14159, 2), c = pow(2, 10), d = log10(1000), e = mod(7, 3)
abs, ceil, floor, round, rint, sqrt, cbrt, pow, exp,
expm1, ln, log, log2, log10, mod, sign, trigonometric
functions, degrees, radians, pi, e, rand, conv, crc32. The
multi-argument forms max(a, b, ...), min(...), sum(...) and avg(...)
outside an aggregation compare or combine their arguments.
Strings
eval a = concat(host, ':', status), b = concat_ws('-', host, level), c = length(host),
d = substring(path, 1, 4), e = upper(level), f = locate('v1', path)
eval r = replace(path, '/api/v[0-9]+', ''), m = regexp_match(path, 'users$')
substring and locate count from 1. replace(s, regex, replacement) uses
a regular expression; like(s, pattern [, case_sensitive]) and ilike match
SQL patterns. Regular expressions are RE2.
Conditions
eval class = if(status >= 500, 'error', 'ok'), who = coalesce(user, 'anonymous')
eval y = nullif(status, 200)
where isnotnull(user) AND NOT isblank(user)
See Expressions.
Date and time
eval day = date_format(_time, '%Y-%m-%d'), h = hour(_time), dow = dayofweek(_time)
eval t = strftime(_time, '%H:%M:%S'), epoch = unix_timestamp(_time)
eval age_days = datediff(now(), _time), mins = timestampdiff(MINUTE, start, _time)
eval prev = date_sub(_time, INTERVAL 1 DAY), next = adddate(_time, 1)
where latest('-1d', _time)
date_formatandtime_formattake MySQL-style%specifiers (%Y-%m-%d %H:%i:%s);strftimetakes POSIX specifiers (%Y-%m-%d %H:%M:%S).earliest('-1d@d', _time)andlatest('-1h', _time)compare a time with a time modifier.- Times are UTC.
convert_tzconverts between zones. extract(hour from _time),timestampadd(DAY, 1, _time)andtimestampdiff(SECOND, a, b)take a unit keyword as their first argument.
Type conversion
eval s = cast(status as string), n = tonumber('ff', 16), c = tostring(1234567, 'commas')
See Data types.
JSON
eval id = json_extract(payload, '$.user.id'), keys = json_keys(payload), ok = json_valid(payload)
eval o = json_object('host', host, 'status', status), a = json_array(1, 2, 3)
JSON functions take and return JSON text. json_set, json_delete,
json_append and json_extend return the modified text. The spath
command extracts JSON values into columns.
Arrays and lambdas
eval tags = split(tag_list, ','), n = array_length(tags), first = mvindex(tags, 0)
eval doubled = transform(values, x -> x * 2), big = filter(values, x -> x > 10)
eval total = reduce(values, 0, (acc, x) -> acc + x), any_big = exists(values, x -> x > 10)
mvindex and mvfind count from 0. Lambdas (x -> expr,
(acc, x) -> expr) are valid only as arguments of forall, exists,
filter, transform and reduce. The lambda of transform takes the
element, or the element and its 0-based index:
makeresults count=1 | eval a = array(10, 20, 30) | eval d = transform(a, x -> x * 2), i = transform(a, (x, i) -> x + i) | fields a, d, i
a d i
[10,20,30] [20,40,60] [10,21,32]
mvmap(field, expr) evaluates expr with the field name standing for each
element.
IP, hashing, system
where cidrmatch(client_ip, '10.0.0.0/8')
eval h = md5(user), s = sha2(user, 256), t = typeof(status)
cidrmatch accepts IPv4 and IPv6. typeof returns the run-time type name
(see Data types).
Relevance functions
match, match_phrase and match_phrase_prefix filter one field by its
tokens; LynxDB computes no scores. See
Search semantics.