OceanBase Database provides a collection of array functions for processing array data. You can use these functions to execute complex array queries and analysis.
The following table describes the array functions supported by OceanBase Database.
Array functions
| Function type | Description |
|---|---|
| Array constructor functions | Construct an array object. |
| Array judgment functions | Perform logic judgment on the input array data and return a Boolean value. |
| Array output functions | Output input array data and returning the output array data. |
| Array manipulation functions | Perform basic operations on input array data, such as adding elements, deleting elements, and finding elements, and returning the resulting array data. |
Overview of array functions
The following are all the array functions currently supported by OceanBase Database:
| Function type | Function name | Description |
|---|---|---|
| Array constructor function | array | Constructs an array data. |
| Array constructor function | rb_build | Converts array data with a base type of integer into highly compressed bitmap data (RoaringBitmap). |
| [Operator | [] | Constructs an array data. |
| Array decision function | array_contains | Determines whether an array contains a specific element. |
| Array decision function | array_contains_all | Determines whether the input array contains all elements of another array. |
| Array decision function | array_overlaps | Determines whether two arrays have an intersection. |
| [Operator | ANY | Determines whether an array contains a certain 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 basic elements in the array into a string according to the specified delimiter and null element symbol. |
| Array manipulation function | array_append | Adds a specified element to the target array. |
| Array manipulation function | array_distinct | Performs deduplication operations on the target array. |
| Array manipulation function | array_remove | Removes specified elements 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 the specified position in the target array based on the index. |
| Array manipulation function | string_to_array | Splits a string into an array of strings based on the specified delimiter. |
| Array manipulation function | array_agg | Aggregates multiple rows of data in a specified column into a single array value and return the result. |
| Array manipulation function | unnest | Expands elements in an array into multiple rows and returns a relational table containing these elements. |
| Array manipulation function | array_prepend | Adds an element at the beginning of an array and return a new array. |
| Array manipulation function | array_concat | Merges multiple arrays and return a new array. |
| Array manipulation function | array_compact | Removes consecutive duplicate elements in an array and return a new array. |
| Array manipulation function | array_sort | Sorts an array in ascending order, placing NULL values at the end of the array during sorting. |
| Array manipulation function | array_length | Returns the length of an array. If it is a nested array, it returns the length of the first-level array. |
| Array manipulation function | array_range | Generates an array of an arithmetic sequence. |
| Array manipulation function | array_sum | Calculates the sum of all elements in an array. |
| Array manipulation function | array_diffrence | Calculates the difference between two adjacent elements in an array and return the result stored in a new array. |
| Array manipulation function | array_min | Returns the minimum value in an array, NULL values are ignored. |
| Array manipulation function | array_max | Returns the maximum value in an array, NULL values are ignored. |
| Array manipulation function | array_avg | Returns the average value of all elements in an array, where NULL values are treated as 0 for calculation. |
| 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 a specified position in an array and return the extracted elements as a new array. |
| Array manipulation function | reverse | Reverses the order of elements in an array. If it is a nested array, the elements of the first level are reversed. |
| Array manipulation function | array_map | Transforms each element of the input arrays according to a Lambda function, and combine all the new elements obtained from each transformation into a single new array as the output. |
| Array manipulation function | array_filter | Determines whether to filter elements in an array based on the return value of a Lambda function. |
| Array manipulation function | array_sortby | Sorts an array based on a given Lambda function. |
| Array manipulation function | array_first | Returns the first element in the array that makes the given Lambda function return a non-zero value. |