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

Given the contents of a web request User-Agent header string, determine which

drop_fields

Drop one or more fields from an object.

embed_sql_params

Embed parameters into the given prepared SQL statement by replacing occurrences

get_field

Given an object, and a computed string value as key (which can be a column

get_item

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

index_of_item

Given an array and a value, return the index of the first occurrence of that

make_array

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

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.

match_regex_all

Returns an array containing all parts of the argument string that match the

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

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_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, constructed by starting at the

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.