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

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

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 a key, which can be a column value or some string concatenation, for example, look up whether that key exists in that object.

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 nothing matches.

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 found in the array. The index of the first element in an array is 0.

make_array

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

make_fields

Extend an existing object with new fields.

match_regex

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

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

path_exists

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

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.

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 a separator, which is treated as a set of characters.

unpivot_array

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