Array functions are a set of specialized functions provided by the OceanBase Database system for handling array data. These functions allow users to perform complex array queries and analyses.
Here is an overview of some array functions in OceanBase Database and their purposes:
Types of array functions
| Array Function Type | Description |
|---|---|
| Array constructor functions | Functions used to construct new array data types. |
| Array decision functions | Functions that perform logical judgments on input array data and return boolean values. |
| Array output functions | Functions that output input array data and return the output array data. |
| Array manipulation functions | Functions that perform basic operations on input array data, such as adding, removing, and finding elements, and return the modified array data. |
Complete list of array functions
The following table lists all array functions supported by OceanBase Database:
| Function Type | Function Name | Description |
|---|---|---|
| Array constructor function | array | Constructs an array data type. |
| Array constructor function | rb_build | Converts an array data type with an integer base type to a RoaringBitmap data type. |
| Operator | [] | Constructs an array data type. |
| Array decision function | array_contains | Checks whether an array contains a specified element. |
| Array decision function | array_contains_all | Checks whether an input array contains all elements of another array. |
| Array decision function | array_overlaps | Checks whether two arrays have an intersection. |
| Operator | ANY | Checks whether an array contains a specified element, with the same functionality as the array_contains() function. |
| Array output function | array_to_string | Converts an array to a string. Specifically, it prints all base elements of the array into a string based on the specified separator and empty element symbol. |
| Array manipulation function | array_append | Adds a specified element to the target array. |
| Array manipulation function | array_distinct | Removes duplicate elements from the target array. |
| Array manipulation function | array_remove | Removes a specified element from the target array. |
| Array manipulation function | cardinality | Returns the number of base elements in the target array. |
| Array manipulation function | element_at | Returns the element at a specified index in the target array. |
| Array manipulation function | string_to_array | Splits a string into an array of strings based on a specified separator. |
| Array manipulation function | array_agg | Aggregates multiple rows of data from a specified column into a single array value and returns the result. |
| Array manipulation function | unnest | Expands elements of an array into multiple rows and returns a relation table containing these elements. |
| Array manipulation function | array_prepend | Adds an element to the beginning of an array and returns a new array. |
| Array manipulation function | array_concat | Merges multiple arrays into a single array and returns the result. |
| Array manipulation function | array_compact | Removes consecutive duplicate elements from an array and returns a new array. |
| Array manipulation function | array_sort | Sorts an array in ascending order and places NULL values at the end of the array during sorting. |
| Array manipulation function | array_length | Returns the length of an array. If the array is nested, it returns the length of the top-level array. |
| Array manipulation function | array_range | Generates an arithmetic sequence array. |
| Array manipulation function | array_sum | Calculates the sum of all elements in an array. |
| Array manipulation function | array_difference | Calculates the difference between two adjacent elements in an array and stores the result in a new array. |
| Array manipulation function | array_min | Returns the minimum value in an array, ignoring NULL values. |
| Array manipulation function | array_max | Returns the maximum value in an array, ignoring NULL values. |
| Array manipulation function | array_avg | Calculates the average of all elements in an array, treating NULL values as 0. |
| Array manipulation function | array_position | Finds the position of a specified element in an array. |
| Array manipulation function | array_slice | Extracts a portion of elements from an array starting at a specified position and returns a new array containing the extracted elements. |
| Array manipulation function | reverse | Reverses the order of elements in an array. If the array is nested, it reverses the order of elements at the top level. |
| Array manipulation function | array_map | Converts each element of an array based on a Lambda function and returns a new array containing all the converted elements. |
| Array manipulation function | array_filter | Filters elements of an array based on the return value of a Lambda function. |
| Array manipulation function | array_sortby | Sorts an array based on a specified Lambda function. |
| Array manipulation function | array_first | Returns the first element in an array that makes the return value of a specified Lambda function not equal to 0. |