All OPAL functions¶
Opal functions compute values for individual columns.
Function |
Description |
---|---|
Returns the absolute value of ‘val’. |
|
Return any value of one column across a group |
|
Return any non-null value of one column across a group. |
|
Returns an array containing all elements from the source array as well as the new element. |
|
Convert a datum into an array or NULL if conversion is impossible |
|
Returns an array of concatenated input values. |
|
Returns an array of distinct input values. |
|
Returns |
|
Returns a new array that contains only the distinct elements from the input array after removing duplicate elements. |
|
returns the number of elements in an array, or null if input is not an array |
|
Return the maximum value in an array. |
|
Return the minimum value in an array. |
|
Returns a null value of type array. |
|
Cast all values in an array to |
|
Returns an array that contains the [multiset](https://en. |
|
Compares whether two arrays have at least one element in common. |
|
Specify the ordering as ascending when processing data. |
|
Calculate the arithmetic average of the input expression across the group, or of the scalar arguments if more than one. |
|
Returns the end time of the current bin (exclusive). |
|
Returns the start time of the current bin (inclusive). |
|
Generate a boolean value of the argument value. |
|
Returns a null value of type bool. |
|
Return a matching |
|
Returns ‘val’ rounded up to the given ‘precision’. |
|
Return the first non-null argument or null if all are null. |
|
Returns a concatenation of N arrays |
|
Return the concatenation of all string arguments. |
|
Returns true if the |
|
Count the number of non-null items in the group. |
|
Estimate the approximate number of distinct values in the input using hyper-log-log. |
|
Count the exact number of distinct values in the input using complete enumeration (slower than [count_distinct](. |
|
DecodeBase64 decodes a base64 encoded input |
|
Replace %-encoded escape sequences in a string with unencoded plain text. |
|
Replace all %-encoded escape sequences in a string with unencoded plain text. |
|
Returns the dense rank within an ordered group of values. |
|
Calculate the average per-second derivative of the argument across the group. |
|
Specify the ordering as descending when processing data. |
|
Identify the browser used for a web request from its |
|
Drop one or more fields from an object. |
|
Convert one argument as a number of nanoseconds or a timestamp, or two arguments as start or stop timestamps, to a duration. |
|
The input is a number representing a number of hours, the output is that quantity as type duration. |
|
The input is a number representing a number of minutes, the output is that quantity as type duration. |
|
The input is a number representing a number of milliseconds, the output is that quantity as type duration. |
|
Returns a null value of type duration. |
|
The input is a number representing a number of seconds, the output is that quantity as type duration. |
|
Returns the Levenshtein distance between |
|
Embed parameters into the given prepared SQL statement by replacing occurrences of the placeholder character ‘? |
|
EncodeBase64 encodes the input in the base64 format |
|
Replace certain characters in a string with %-encoded escape sequences. |
|
Replace certain characters in a string with %-encoded escape sequences. |
|
Returns true if string |
|
Return true if A is equal to B. |
|
Returns Euler’s number e raised to the given number. |
|
Return the first value of one column across an ordered group. |
|
Return the first non-null value of one column across an ordered group. |
|
Generate a float representation of the argument value. |
|
Returns a null value of type float64. |
|
Returns ‘val’ rounded down to the given ‘precision’. |
|
Format the timestamp value in UTC according to the specified format. |
|
Specify the approximate relative time frame for a window context. |
|
Specify the exact relative time frame for a window context. |
|
Given a numeric value representing milliseconds since epoch, return a timestamp of that point in time. |
|
Given a numeric value representing nanoseconds since epoch, return a timestamp of that point in time. |
|
Given a numeric value representing seconds since epoch, return a timestamp of that point in time. |
|
Given an object, and a computed string value as key (which can be a column value or some string concatenation, for example), look |
|
Given an array and a computed index, return the item at that index in the array. |
|
Process an object or array expression using a query written in the JMESPath query language. |
|
Returns a string that matches the given regular expression (which may be null if nothing matches. |
|
Returns an array containing all parts of the argument string that match the given regular expression (which may be empty if |
|
Grouping/partitioning in which to process data. |
|
Return true if A is strictly greater than B. |
|
Return true if A is greater than or equal to B. |
|
Accepts a variable number of arguments of arbitrary types and returns a signed 64-bit hash. |
|
Computes the great circle approximate distance between two latitude/longitude coordinates, using the haversine formula. |
|
Return the second argument if condition# is true, otherwise return third argument. |
|
Return the second argument if the first argument has the null value. |
|
Returns true if the expression matches with any value in the given set. |
|
Given an array and a value, return the index of the first occurrence of that value within that array, or NULL if the value isn’t |
|
Returns an array containing all elements from the source array as well as the new element. |
|
Generate an integer representation of the argument value. |
|
Returns a null value of type int64. |
|
Given an integer that represents an IPv4 address in big-endian (network) byte order, return the corresponding dotted-quad |
|
Returns the integer quotient of the dividend divided by the divisor and truncates the remainder. |
|
Returns an IPv4 address representation of the argument value. |
|
Test whether an IPv4 address is in a given subnet. |
|
Given an IPv4 address string in dotted-quad format, possibly with a slash-masksize trailing component, convert the address to an |
|
Given an IPv4 address string in dotted-quad format, convert the address to an integer representing the address in big-endian |
|
Return true if the argument has the null value. |
|
Return the value of one column in a previous row across an ordered group. |
|
Return the non-null value of one column in a previous row across an ordered group. |
|
Return the last value of one column across an ordered group. |
|
Return the last non-null value of one column across an ordered group. |
|
Return the value of one column in a following row across an ordered group. |
|
Return the non-null value of one column in a following row across an ordered group. |
|
Returns a leftmost substring of its input. |
|
Returns true if subject matches pattern (case-sensitive). |
|
Returns natural logarithm of a numeric expression. |
|
Returns logarithm of a numeric expression (second argument) with the provided base (first argument). |
|
Return the input string in lowercase. |
|
Left pads a string with characters from another string, default pad string is whitespace |
|
Return true if A is strictly less than B. |
|
Return true if A is less than or equal to B. |
|
|
|
Select a metric for the align verb. |
|
Returns an array constructed from zero, one or more input arguments. |
|
Returns an array that contains a list of consecutive integers ranging from |
|
Extend an existing object with new fields. |
|
Turn a sequence of name:value elements into an object. |
|
Return true if the argument input string or object (converted to a string) matches the argument regular expression. |
|
Compute the maximum of one column across a group (with one argument) or the scalar greatest value of its arguments (with more than |
|
Return the fast approximate median value of one column. |
|
Return the exact median value of one column. |
|
Merge one or more objects into a single object. |
|
Select the metrics in the rollup verb. |
|
Compute the minimum of one column across a group (with one argument) or the scalar least value of its arguments (with more than |
|
Returns the remainder when dividend is divided by the divisor. |
|
Return true if A is not equal to B. |
|
Returns the current time for which the query is executed as timestamp. |
|
Specify the ordering as nulls first when processing data. |
|
Specify the ordering as nulls last when processing data. |
|
Returns a null value of type numeric. |
|
Convert a datum into an object or NULL if conversion is impossible |
|
Returns one OBJECT per group. |
|
Get array of object keys (field names from object). |
|
Returns a null value of type object. |
|
|
|
Specify options to change the verb’s behavior |
|
Specify the ordering when processing data. |
|
Parses an input string as character-separated values, where the default separator is a comma (comma-separated values. |
|
Given a duration string (like “2h 30m”, “1. |
|
Parses a string encoded hex number and returns an int64. |
|
When the input is an IPv(4/6) address, returns a JSON object containing the following attributes - family (either “4” or “6”) |
|
Parse a YYYY-MM-DDTHH:MM:SSZ-formatted string as a timestamp. |
|
Parse the argument value as a JSON string. |
|
Returns an object of key=value pairs extracted from an input string. |
|
Parse a string value, whose format is specified by the |
|
Returns a JSON object consisting of all the components (fragment, host, path, port, query, scheme). |
|
Given a column and path, return whether the JSON path exists in that column. |
|
Returns an approximated value for the specified percentile of the input expression across the group. |
|
Assuming a continuous distribution, it returns the value for the specified percentile of the input expression across the group. |
|
Assuming a discrete distribution, it returns the value for the specified percentile of the input expression across the group. |
|
Pick one or more fields from an object. |
|
Converts an array of “key”-“value” pairs into an object with key-value attributes. |
|
Searches for the first occurrence of the second argument (needle) in the first argument (haystack) and, if successful, returns the |
|
Returns a number ‘base’ raised to the specified power ‘exponent’. |
|
Returns an array containing all elements from the source array as well as the new element. |
|
Specify the primary key for some verbs |
|
prom_quantile calculates an approximate percentile value based on the distribution in a histogram metric that was generated by a |
|
Returns the latest time of the query time window. |
|
Returns the earliest time of the query time window. |
|
Returns the rank within an ordered group of values. |
|
Calculate the average per-second rate of the argument across the group. |
|
Coerce a string literal to a regular expression. |
|
Replaces all instances of the substring in the input string with a provided value. |
|
Replaces all instances of a matched regex pattern in the input string with a provided value. |
|
Returns a rightmost substring of its input. |
|
Returns ‘val’ rounded to the given ‘precision’. |
|
Returns the value of the “Valid To” column, or null if the verb input dataset has no “Valid To” column. |
|
Return the window index of the row within its groupby, when ordered by the orderby. |
|
Returns the value of the timestamp column for Event datasets, the “Valid From” column for Interval or Resource datasets, or null |
|
Right pads a string with characters from another string, default pad string is whitespace |
|
|
|
Return true if A is the same as B. |
|
Return true if the |
|
Given an array, returns a possibly smaller array, starting at the given |
|
Return an array with the elements of the input array in sorted order. |
|
Splits the string into an array, based on the separator. |
|
Splits a given string at a specified character and returns the requested part. |
|
Returns the square root for a given input and null if input is negative. |
|
Returns true if string starts with expr. |
|
Calculate the standard deviation across the group. |
|
Generate a string representation of the argument value. |
|
Returns concatenated input values, separated by the delimiter. |
|
Returns concatenated input values, separated by the delimiter. |
|
Returns a null value of type string. |
|
Compute the length of an input string. |
|
Extracts characters from a string, starting at an index. |
|
Calculate the sum of the argument across the group, or of the scalar arguments if more than one. |
|
Returns a null value of type timestamp. |
|
Convert a duration to a number of days. |
|
Convert a duration to a number of hours. |
|
Convert a duration to a number of milliseconds. |
|
Convert a duration to a number of minutes. |
|
Convert a duration to a number of nanoseconds. |
|
Convert a duration to a number of seconds. |
|
Convert a duration to a number of weeks. |
|
Splits the string into an array based on separator, which is treated as a set of characters. |
|
Tokenizes the input string using the delimiter and returns the requested part. |
|
Returns an approximation of the top K most frequent values in the input, along with their approximate frequencies. |
|
|
|
Convert an object into an array of “key”-“value” pairs. |
|
Return the input string in uppercase. |
|
Specify the validity period for each event for some verbs |
|
Returns a null value of type variant. |
|
Given a variant value (typically, something you get from looking up a path or element in a JSON object or array,) determine what |
|
Construct equi-width histogram by dividing up the range between |
|
Evaluates its argument in windowed context, partitioned over the given grouping and ordered by the given ordering (by default |
Aliases
Aliases work as alternative names for their main function. Users of certain other query languages may be more comfortable with these alternative names. Alias names on this list can always be used whenever the main name can be used.
Alias |
Function |
---|---|