OPAL Semistructured Functions

Semistructured functions act upon, or produce, arrays or objects that are treated as scalars. E.g., a single column may contain multiple values in an array, or multiple key/value tuples in an object.

The values inside semi-structured containers are stored as variants, and must be cast to the type you expect them to have. If the array or object contains a value of an unexpected type, the cast returns null.

Function

Description

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_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.

concat_arrays

Returns a concatenation of N arrays array1, array2, .

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.

embed_sql_params

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

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_all

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

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.

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.

merge_objects

Merge one or more objects into a single object.

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.

parse_csv

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

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_json

Parse the argument value as a JSON string.

parse_kvs

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

parse_url

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

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.

prepend_item

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

slice_array

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

split

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

tokenize

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

topk_agg

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

unpivot_array

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