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.