Skip to main content

Functions

Every PPL scalar function name. A name maps onto an IR registry function, is lowered onto IR operators, or is unsupported and fails with UNSUPPORTED_FUNCTION. NULL behavior and errors are those of the IR function.

FunctionCategorySupportSemantics
absmathfullAbsolute value.
acosmathfullTrigonometric or angle-conversion function acos in radians; NULL outside its domain.
addfulllowered to arithmetic operator +
adddatefulllowered to date_add
addtimedatetimefullt plus the time of day or duration delta.
arraycollectionfullArray of the arguments.
array_lengthcollectionfullNumber of elements of a; NULL for non-arrays.
asciistringfullCode point of the first character of s, 0 for the empty string.
asinmathfullTrigonometric or angle-conversion function asin in radians; NULL outside its domain.
atanmathfullArc tangent of x, or with two arguments of y/x using the signs of both to pick the quadrant.
atan2mathfullArc tangent of y/x using the signs of both to pick the quadrant.
avgmathfullArithmetic mean of the arguments.
cbrtmathfullCube root.
ceilmathfullSmallest integer not less than x.
ceilingmathfullSmallest integer not less than x.
cidrmatchipfullTRUE when the IPv4 or IPv6 address ip is inside the CIDR block cidr; NULL when either does not parse.
coalesceconditionfullThe first argument that is not NULL, or NULL.
concatstringfullConcatenation of the arguments' string forms; NULL when any argument is NULL.
concat_wsstringfullConcatenation of the non-NULL arguments separated by sep.
convmathfullx read as a number in from_base, written in to_base (2-36).
convert_tzdatetimefullt read as wall-clock time in from_tz, rewritten as wall-clock time in to_tz; NULL for unknown zones.
cosmathfullTrigonometric or angle-conversion function cos in radians; NULL outside its domain.
coshmathfullTrigonometric or angle-conversion function cosh in radians; NULL outside its domain.
cotmathfullTrigonometric or angle-conversion function cot in radians; NULL outside its domain.
crc32mathfullCRC-32 (IEEE) checksum of the UTF-8 bytes of s.
curdatedatetimefullThe start (00:00 UTC) of the reference time's day.
current_datedatetimefullThe start (00:00 UTC) of the reference time's day.
current_timedatetimefullThe reference time's time of day as a duration since midnight UTC.
current_timestampdatetimefullThe query's reference time.
curtimedatetimefullThe reference time's time of day as a duration since midnight UTC.
datedatetimefullThe start (00:00 UTC) of t's day; t may be a timestamp or a date or timestamp string.
date_addfulllowered to date_add
date_formatdatetimefullt formatted with MySQL-style % specifiers.
date_subfulllowered to date_add
datediffdatetimefullWhole days from b's date to a's date.
datetimedatetimefullt as a timestamp, converted to time zone to_tz when given.
daydatetimefullDay of month (1-31) of t.
day_of_monthdatetimefullDay of month (1-31) of t.
day_of_weekdatetimefullDay of week of t, 1 = Sunday to 7 = Saturday.
day_of_yeardatetimefullDay of year (1-366) of t.
daynamedatetimefullEnglish name of t's weekday.
dayofmonthdatetimefullDay of month (1-31) of t.
dayofweekdatetimefullDay of week of t, 1 = Sunday to 7 = Saturday.
dayofyeardatetimefullDay of year (1-366) of t.
degreesmathfullTrigonometric or angle-conversion function degrees in radians; NULL outside its domain.
dividefulllowered to arithmetic operator /
emathfullThe constant e.
earliestfulllowered to time comparison
existscollectionfullTRUE when the predicate is TRUE for some element, NULL when none is TRUE but some is NULL.
expmathfulle raised to the power x.
expm1mathfulle raised to the power x, minus 1.
filtercollectionfullElements for which the predicate is TRUE.
floormathfullLargest integer not greater than x.
forallcollectionfullTRUE when the predicate is TRUE for every element (TRUE for an empty array), NULL when none is FALSE but some is NULL.
from_daysdatetimefullThe date n days after year 0.
from_unixtimedatetimefullThe timestamp seconds after the Unix epoch; formatted with MySQL-style specifiers when format is given.
geoipunsupportedLynxDB has no GeoIP database
hourdatetimefullHour (0-23) of t.
hour_of_daydatetimefullHour (0-23) of t.
iffulllowered to CASE
ifnullfulllowered to coalesce
ilikefulllowered to like
isblankfulllowered to is_blank
isemptyfulllowered to is_empty
isnotnullfulllowered to NOT is_null
isnullfulllowered to is_null
ispresentfulllowered to NOT is_null
jsonjsonfulls when it is valid JSON text, else NULL.
json_appendjsonfulls with each value appended to the array at its path.
json_arrayjsonfullJSON array text of the arguments.
json_array_lengthjsonfullNumber of elements of the JSON array s; NULL when s is not a JSON array.
json_deletejsonfulls without the members at the paths.
json_extendjsonfulls with each array value's elements appended to the array at its path.
json_extractjsonfullThe value at a JSON path in s; with several paths a JSON array of the values.
json_keysjsonfullJSON array text of the top-level keys of the JSON object s; NULL for non-objects.
json_objectjsonfullJSON object text from alternating keys and values.
json_setjsonfulls with the value at each path replaced or created.
json_validjsonfullTRUE when s is valid JSON text; FALSE for NULL.
last_daydatetimefullThe start of the last day of t's month.
latestfulllowered to time comparison
leftstringfullThe first n characters of s.
lengthstringfullNumber of characters (Unicode code points) in s.
likefulllowered to like
lnmathfullNatural logarithm; NULL for x <= 0.
localtimedatetimefullThe query's reference time.
localtimestampdatetimefullThe query's reference time.
locatestringfull1-based position of the first occurrence of sub in s at or after start, or 0.
logmathfullNatural logarithm of x, or with two arguments the base-b logarithm of x; NULL for non-positive input.
log10mathfullBase-10 logarithm; NULL for x <= 0.
log2mathfullBase-2 logarithm; NULL for x <= 0.
lowerstringfulls in lower case.
ltrimstringfulls without leading white space.
makedatedatetimefullThe date for a year and day of year.
maketimedatetimefullA time of day from its parts.
matchfulllowered to SEARCH_WORD
match_bool_prefixunsupportedLynxDB has no relevance scoring; use match, match_phrase or match_phrase_prefix on one field, the search command, or where with like or regexp_match
match_phrasefulllowered to SEARCH_PHRASE
match_phrase_prefixfulllowered to SEARCH_PREFIX
maxmathfullLargest argument; NULL arguments are ignored.
md5cryptographicfullHex MD5 digest of s.
microseconddatetimefullMicroseconds (0-999999) of t.
minmathfullSmallest argument; NULL arguments are ignored.
minutedatetimefullMinute (0-59) of t.
minute_of_daydatetimefullMinutes since midnight of t.
minute_of_hourdatetimefullMinute (0-59) of t.
modmathfullRemainder of n divided by m with the sign of n; NULL when m is 0.
modulusmathfullRemainder of n divided by m with the sign of n; NULL when m is 0.
monthdatetimefullMonth (1-12) of t.
month_of_yeardatetimefullMonth (1-12) of t.
monthnamedatetimefullEnglish name of t's month.
multi_matchunsupportedLynxDB has no relevance scoring; use match, match_phrase or match_phrase_prefix on one field, the search command, or where with like or regexp_match
multiplyfulllowered to arithmetic operator *
mvappendcollectionfullArray of the arguments with array arguments flattened one level; NULL arguments are skipped.
mvdedupcollectionfulla with later duplicates removed.
mvfindcollectionfull0-based index of the first element matching regex, or NULL.
mvindexcollectionfullThe element at 0-based start, or with end the inclusive sub-array; negative indexes count from the end.
mvjoincollectionfullString forms of the non-NULL elements joined by sep.
mvmapfulllowered to transform
mvzipcollectionfullPairwise string concatenation of a and b joined by sep (default comma), as long as the shorter.
nowdatetimefullThe query's reference time.
nulliffulllowered to CASE
period_adddatetimefullPeriod YYMM or YYYYMM plus n months, as YYYYMM.
period_diffdatetimefullMonths from period b to period a.
pimathfullThe constant pi.
positionfulllowered to locate
powmathfullx raised to the power y.
powermathfullx raised to the power y.
quarterdatetimefullQuarter (1-4) of t.
query_stringunsupportedLynxDB has no relevance scoring; use match, match_phrase or match_phrase_prefix on one field, the search command, or where with like or regexp_match
radiansmathfullTrigonometric or angle-conversion function radians in radians; NULL outside its domain.
randmathfullUniform random float in [0, 1); with a seed the sequence is reproducible within one query.
reducecollectionfullLeft fold of the elements starting at init, finished by the optional one-parameter lambda.
regexp_matchstringfullTRUE when the RE2 pattern matches anywhere in s.
regexp_replacestringfulls with every match of the RE2 pattern replaced; $1 or \1 in the replacement inserts a group.
replacefulllowered to regexp_replace
reversestringfulls with its characters in reverse order.
rightstringfullThe last n characters of s.
rintmathfullx rounded to the nearest integer, ties to even.
roundmathfullx rounded half away from zero to d decimal places (default 0).
rtrimstringfulls without trailing white space.
sec_to_timedatetimefullseconds as a duration.
seconddatetimefullSecond (0-59) of t.
second_of_minutedatetimefullSecond (0-59) of t.
sha1cryptographicfullHex SHA-1 digest of s.
sha2cryptographicfullHex SHA-2 digest of s with 224, 256, 384 or 512 bits.
signmathfull-1, 0 or 1 according to the sign of x.
signummathfull-1, 0 or 1 according to the sign of x.
simple_query_stringunsupportedLynxDB has no relevance scoring; use match, match_phrase or match_phrase_prefix on one field, the search command, or where with like or regexp_match
sinmathfullTrigonometric or angle-conversion function sin in radians; NULL outside its domain.
sinhmathfullTrigonometric or angle-conversion function sinh in radians; NULL outside its domain.
splitcollectionfulls split at every occurrence of sep; the empty separator splits into characters.
sqrtmathfullSquare root; NULL for negative x.
str_to_datedatetimefulls parsed with MySQL-style % specifiers.
strcmpstringfull-1, 0 or 1 comparing a and b by code points.
strftimedatetimefullt (a timestamp or epoch seconds) formatted with POSIX strftime % specifiers.
subdatefulllowered to date_add
substrstringfullSubstring of s from 1-based position start (negative counts from the end), of at most length characters.
substringstringfullSubstring of s from 1-based position start (negative counts from the end), of at most length characters.
subtimedatetimefullt minus the time of day or duration delta.
subtractfulllowered to arithmetic operator -
summathfullSum of the arguments.
sysdatedatetimefullThe wall-clock time at evaluation.
tanmathfullTrigonometric or angle-conversion function tan in radians; NULL outside its domain.
timedatetimefullt's time of day as a duration since midnight; t may be a timestamp, a duration or a time string.
time_formatdatetimefullThe time-of-day part of t formatted with MySQL-style % specifiers.
time_to_secdatetimefullSeconds of t's time of day.
timediffdatetimefulla minus b as a duration.
timestampdatetimefullt as a timestamp, plus the time of day add when given.
to_daysdatetimefullDays since year 0 of t's date.
to_secondsdatetimefullSeconds since year 0 of t.
tonumberconversionfulls parsed as a number in base (default 10); an int when it has no fraction, else a float.
tostringconversionfullString form of x; formats: hex, binary, commas, duration.
transformcollectionfullArray of the lambda applied to each element (and its 0-based index for a two-parameter lambda).
trimstringfulls without leading and trailing white space.
typeofsystemfullName of the runtime type of x: null, bool, int, float, string, timestamp, duration, array, object.
unix_timestampdatetimefullSeconds since the Unix epoch of t, or of the reference time.
upperstringfulls in upper case.
utc_datedatetimefullThe start (00:00 UTC) of the reference time's day.
utc_timedatetimefullThe reference time's time of day as a duration since midnight UTC.
utc_timestampdatetimefullThe query's reference time.
weekdatetimefullWeek number of t under MySQL week mode 0-7 (default 0).
week_of_yeardatetimefullWeek number of t under MySQL week mode 0-7 (default 0).
weekdaydatetimefullDay of week of t, 0 = Monday to 6 = Sunday.
yeardatetimefullYear of t.
yearweekdatetimefullYear and week of t as YYYYWW under MySQL week mode 0-7 (default 0).