All OPAL functions

Opal functions compute values for individual columns.

Function

Description

abs

Returns the absolute value of ‘val’.

any

Return any value of one column across a group

any_not_null

Return any non-null value of one column across a group.

append_item

Returns an array containing all elements from the source array as well as the new element.

array

Convert a datum into an array or NULL if conversion is impossible

array_agg

Returns an array of concatenated input values.

array_agg_distinct

Returns an array of distinct input values.

array_contains

Returns true if array arr contains a value equal to val, where val is of variant type.

array_distinct

Returns a new array that contains only the distinct elements from the input array after removing duplicate elements.

array_length

returns the number of elements in an array, or null if input is not an array

array_max

Return the maximum value in an array.

array_min

Return the minimum value in an array.

array_null

Returns a null value of type array.

array_to_string

Cast all values in an array to string and concatenate them, optionally adding a separator between each value.

array_union_agg

Returns an array that contains the [multiset](https://en.

arrays_overlap

Compares whether two arrays have at least one element in common.

asc

Specify the ordering as ascending when processing data.

avg

Calculate the arithmetic average of the input expression across the group, or of the scalar arguments if more than one.

bin_end_time

Returns the end time of the current bin (exclusive).

bin_start_time

Returns the start time of the current bin (inclusive).

bool

Generate a boolean value of the argument value.

bool_null

Returns a null value of type bool.

case

Return a matching result if the given condition is true.

ceil

Returns ‘val’ rounded up to the given ‘precision’.

coalesce

Return the first non-null argument or null if all are null.

concat_arrays

Returns a concatenation of N arrays array1, array2, .

concat_strings

Return the concatenation of all string arguments.

contains

Returns true if the str string contains the expr string (case-sensitive).

count

Count the number of non-null items in the group.

count_distinct

Estimate the approximate number of distinct values in the input using hyper-log-log.

count_distinct_exact

Count the exact number of distinct values in the input using complete enumeration (slower than [count_distinct](.

decode_base64

DecodeBase64 decodes a base64 encoded input

decode_uri

Replace %-encoded escape sequences in a string with unencoded plain text.

decode_uri_component

Replace all %-encoded escape sequences in a string with unencoded plain text.

dense_rank

Returns the dense rank within an ordered group of values.

deriv

Calculate the average per-second derivative of the argument across the group.

desc

Specify the ordering as descending when processing data.

detect_browser

Identify the browser used for a web request from its User-Agent header.

drop_fields

Drop one or more fields from an object.

duration

Convert one argument as a number of nanoseconds or a timestamp, or two arguments as start or stop timestamps, to a duration.

duration_hr

The input is a number representing a number of hours, the output is that quantity as type duration.

duration_min

The input is a number representing a number of minutes, the output is that quantity as type duration.

duration_ms

The input is a number representing a number of milliseconds, the output is that quantity as type duration.

duration_null

Returns a null value of type duration.

duration_sec

The input is a number representing a number of seconds, the output is that quantity as type duration.

editdistance

Returns the Levenshtein distance between str1 and str2.

embed_sql_params

Embed parameters into the given prepared SQL statement by replacing occurrences of the placeholder character ‘?

encode_base64

EncodeBase64 encodes the input in the base64 format

encode_uri

Replace certain characters in a string with %-encoded escape sequences.

encode_uri_component

Replace certain characters in a string with %-encoded escape sequences.

ends_with

Returns true if string str ends with string expr.

eq

Return true if A is equal to B.

exp

Returns Euler’s number e raised to the given number.

first

Return the first value of one column across an ordered group.

first_not_null

Return the first non-null value of one column across an ordered group.

float64

Generate a float representation of the argument value.

float64_null

Returns a null value of type float64.

floor

Returns ‘val’ rounded down to the given ‘precision’.

format_time

Format the timestamp value in UTC according to the specified format.

frame

Specify the approximate relative time frame for a window context.

frame_exact

Specify the exact relative time frame for a window context.

from_milliseconds

Given a numeric value representing milliseconds since epoch, return a timestamp of that point in time.

from_nanoseconds

Given a numeric value representing nanoseconds since epoch, return a timestamp of that point in time.

from_seconds

Given a numeric value representing seconds since epoch, return a timestamp of that point in time.

get_field

Given an object, and a computed string value as key (which can be a column value or some string concatenation, for example), look

get_item

Given an array and a computed index, return the item at that index in the array.

get_jmespath

Process an object or array expression using a query written in the JMESPath query language.

get_regex

Returns a string that matches the given regular expression (which may be null if nothing matches.

get_regex_all

Returns an array containing all parts of the argument string that match the given regular expression (which may be empty if

group_by

Grouping/partitioning in which to process data.

gt

Return true if A is strictly greater than B.

gte

Return true if A is greater than or equal to B.

hash

Accepts a variable number of arguments of arbitrary types and returns a signed 64-bit hash.

haversine_distance_km

Computes the great circle approximate distance between two latitude/longitude coordinates, using the haversine formula.

if

Return the second argument if condition# is true, otherwise return third argument.

if_null

Return the second argument if the first argument has the null value.

in

Returns true if the expression matches with any value in the given set.

index_of_item

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

insert_item

Returns an array containing all elements from the source array as well as the new element.

int64

Generate an integer representation of the argument value.

int64_null

Returns a null value of type int64.

int64_to_ipv4

Given an integer that represents an IPv4 address in big-endian (network) byte order, return the corresponding dotted-quad

int_div

Returns the integer quotient of the dividend divided by the divisor and truncates the remainder.

ipv4

Returns an IPv4 address representation of the argument value.

ipv4_address_in_network

Test whether an IPv4 address is in a given subnet.

ipv4_network_int64

Given an IPv4 address string in dotted-quad format, possibly with a slash-masksize trailing component, convert the address to an

ipv4_to_int64

Given an IPv4 address string in dotted-quad format, convert the address to an integer representing the address in big-endian

is_null

Return true if the argument has the null value.

lag

Return the value of one column in a previous row across an ordered group.

lag_not_null

Return the non-null value of one column in a previous row across an ordered group.

last

Return the last value of one column across an ordered group.

last_not_null

Return the last non-null value of one column across an ordered group.

lead

Return the value of one column in a following row across an ordered group.

lead_not_null

Return the non-null value of one column in a following row across an ordered group.

left

Returns a leftmost substring of its input.

like

Returns true if subject matches pattern (case-sensitive).

ln

Returns natural logarithm of a numeric expression.

log

Returns logarithm of a numeric expression (second argument) with the provided base (first argument).

lower

Return the input string in lowercase.

lpad

Left pads a string with characters from another string, default pad string is whitespace

lt

Return true if A is strictly less than B.

lte

Return true if A is less than or equal to B.

ltrim

ltrim removes leading characters from a string.

m

Select a metric for the align verb.

make_array

Returns an array constructed from zero, one or more input arguments.

make_array_range

Returns an array that contains a list of consecutive integers ranging from start to end.

make_fields

Extend an existing object with new fields.

make_object

Turn a sequence of name:value elements into an object.

match_regex

Return true if the argument input string or object (converted to a string) matches the argument regular expression.

max

Compute the maximum of one column across a group (with one argument) or the scalar greatest value of its arguments (with more than

median

Return the fast approximate median value of one column.

median_exact

Return the exact median value of one column.

merge_objects

Merge one or more objects into a single object.

metric

Select the metrics in the rollup verb.

min

Compute the minimum of one column across a group (with one argument) or the scalar least value of its arguments (with more than

mod

Returns the remainder when dividend is divided by the divisor.

ne

Return true if A is not equal to B.

now

Returns the current time for which the query is executed as timestamp.

nullsfirst

Specify the ordering as nulls first when processing data.

nullslast

Specify the ordering as nulls last when processing data.

numeric_null

Returns a null value of type numeric.

object

Convert a datum into an object or NULL if conversion is impossible

object_agg

Returns one OBJECT per group.

object_keys

Get array of object keys (field names from object).

object_null

Returns a null value of type object.

on

on is a special function used to specify the join condition for some join verbs.

options

Specify options to change the verb’s behavior

order_by

Specify the ordering when processing data.

parse_csv

Parses an input string as character-separated values, where the default separator is a comma (comma-separated values.

parse_duration

Given a duration string (like “2h 30m”, “1.

parse_hex

Parses a string encoded hex number and returns an int64.

parse_ip

When the input is an IPv(4/6) address, returns a JSON object containing the following attributes - family (either “4” or “6”)

parse_isotime

Parse a YYYY-MM-DDTHH:MM:SSZ-formatted string as a timestamp.

parse_json

Parse the argument value as a JSON string.

parse_kvs

Returns an object of key=value pairs extracted from an input string.

parse_timestamp

Parse a string value, whose format is specified by the format argument, as a timestamp.

parse_url

Returns a JSON object consisting of all the components (fragment, host, path, port, query, scheme).

path_exists

Given a column and path, return whether the JSON path exists in that column.

percentile

Returns an approximated value for the specified percentile of the input expression across the group.

percentile_cont

Assuming a continuous distribution, it returns the value for the specified percentile of the input expression across the group.

percentile_disc

Assuming a discrete distribution, it returns the value for the specified percentile of the input expression across the group.

pick_fields

Pick one or more fields from an object.

pivot_array

Converts an array of “key”-“value” pairs into an object with key-value attributes.

position

Searches for the first occurrence of the second argument (needle) in the first argument (haystack) and, if successful, returns the

pow

Returns a number ‘base’ raised to the specified power ‘exponent’.

prepend_item

Returns an array containing all elements from the source array as well as the new element.

primary_key

Specify the primary key for some verbs

prom_quantile

prom_quantile calculates an approximate percentile value based on the distribution in a histogram metric that was generated by a

query_end_time

Returns the latest time of the query time window.

query_start_time

Returns the earliest time of the query time window.

rank

Returns the rank within an ordered group of values.

rate

Calculate the average per-second rate of the argument across the group.

regex

Coerce a string literal to a regular expression.

replace

Replaces all instances of the substring in the input string with a provided value.

replace_regex

Replaces all instances of a matched regex pattern in the input string with a provided value.

right

Returns a rightmost substring of its input.

round

Returns ‘val’ rounded to the given ‘precision’.

row_end_time

Returns the value of the “Valid To” column, or null if the verb input dataset has no “Valid To” column.

row_number

Return the window index of the row within its groupby, when ordered by the orderby.

row_timestamp

Returns the value of the timestamp column for Event datasets, the “Valid From” column for Interval or Resource datasets, or null

rpad

Right pads a string with characters from another string, default pad string is whitespace

rtrim

rtrim removes trailing characters from a string.

same

Return true if A is the same as B.

search

Return true if the for text is matched in the input string in (case-insensitive).

slice_array

Given an array, returns a possibly smaller array, starting at the given from index, and including items up to *but not

sort_array

Return an array with the elements of the input array in sorted order.

split

Splits the string into an array, based on the separator.

split_part

Splits a given string at a specified character and returns the requested part.

sqrt

Returns the square root for a given input and null if input is negative.

starts_with

Returns true if string starts with expr.

stddev

Calculate the standard deviation across the group.

string

Generate a string representation of the argument value.

string_agg

Returns concatenated input values, separated by the delimiter.

string_agg_distinct

Returns concatenated input values, separated by the delimiter.

string_null

Returns a null value of type string.

strlen

Compute the length of an input string.

substring

Extracts characters from a string, starting at an index.

sum

Calculate the sum of the argument across the group, or of the scalar arguments if more than one.

timestamp_null

Returns a null value of type timestamp.

to_days

Convert a duration to a number of days.

to_hours

Convert a duration to a number of hours.

to_milliseconds

Convert a duration to a number of milliseconds.

to_minutes

Convert a duration to a number of minutes.

to_nanoseconds

Convert a duration to a number of nanoseconds.

to_seconds

Convert a duration to a number of seconds.

to_weeks

Convert a duration to a number of weeks.

tokenize

Splits the string into an array based on separator, which is treated as a set of characters.

tokenize_part

Tokenizes the input string using the delimiter and returns the requested part.

topk_agg

Returns an approximation of the top K most frequent values in the input, along with their approximate frequencies.

trim

trim removes leading and trailing characters from a string.

unpivot_array

Convert an object into an array of “key”-“value” pairs.

upper

Return the input string in uppercase.

valid_for

Specify the validity period for each event for some verbs

variant_null

Returns a null value of type variant.

variant_type_name

Given a variant value (typically, something you get from looking up a path or element in a JSON object or array,) determine what

width_bucket

Construct equi-width histogram by dividing up the range between min and max with num_buckets buckets, and compute an integer

window

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.