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.