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_contains	Tests if an array contains a value.
array_distinct	Returns a new array that contains only the distinct elements from the input array after removing duplicate elements.
array_length	returns the number of elements in an array, or null if input is not an array
array_max	Return the maximum value in an array.
array_min	Return the minimum value in an array.
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.
array_union_agg	Returns an array containing the multiset union of input arrays.
arrays_overlap	Compares whether two arrays have at least one element in common.
concat_arrays	Returns a concatenation of N arrays array1, array2, arrayN.
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.
intersect_arrays	Returns an array containing the matching elements inside the two input arrays.
make_array	Returns an array constructed from zero, one or more input arguments.
make_array_range	Returns an array that contains a list of consecutive integers ranging from start to end.
make_fields	Extend an existing object with new fields.
make_object	Turn a sequence of name:value elements into an object.
merge_objects	Merge one or more objects into a single object.
object	Convert a datum into an object or NULL if conversion is impossible
object_agg	Extract aggregated fields and values from a group of rows into a new JSON object.
object_keys	Get an array of top-level keys from an object type field (field names from JSON objects).
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	Parse an IPv4 or IPv6 network address into relevant attributes.
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.
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
sort_array	Return an array with the elements of the input array in sorted order.
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.
topk_agg	Returns an approximation of the top K most frequent values in the input, along with their approximate frequencies.
unpivot_array	Convert an object into an array of “key”-“value” pairs.