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.