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

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

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.

unpivot_array

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