Array Functions
Array functions supported by e6data
SUBSCRIPT OPERATOR []
The Subscript operator is used to access the elements of an array. The index starts with 1
ELEMENT_AT ( <array_expr>, index )
The element_at function returns element of array at a given index. The index starts with 1
ARRAY_POSITION( <expr>, <array_expr> )
Returns the position of the passed argument in an array
Supported datatype: INT/DOUBLE/VARCHAR/BOOLEAN
SIZE( <array_expr> )
Returns the size of the input array.
ARRAY_TO_STRING( distinct <expr> )
Returns an ARRAY of distinct values
Limitations - Complex datatype are not supported
ARRAY_AGG( [distinct] <array_expr> )
Concatenates the elements of specified array expression with the provided delimiter.
ARRAY_APPEND( <array_expr>, new_element )
Adds a new element at the end of array .
new_element should be of same datatype which is present in the array
Supported Datatypes, array
ARRAY_PREPEND( <array_expr>, new_element )
Adds a new element to the beginning of the array .
new_element should be of same datatype which is present in the array
Supported Datatypes, array
ARRAY_CONCAT( <array_expr1>, <array_expr2>, [array_expr3] )
Appends given arrays .
Supported Datatypes, array
ARRAY_CONTAINS( <array_expr>, <value> )
If the specified value is present in the designated ARRAY, the function returns TRUE.
Supported Datatypes, array
ARRAY_JOIN( <array_expr>, <value> )
Concatenates the elements of the array using the provided delimiter. If a Null replacement is defined, any Null values in the array are substituted with the specified Null replacement.
Supported Datatypes, array
ARRAY_SLICE( <array_expr>, <from_index>, <to> )
Returns an array constructed from a specified subset of elements of the input array.
Supported Datatypes, array
The output result comprises elements from the
from index
uptoto
parameter but excluding the element identified by theto
parameter.Array starts with index 1.
If array, from index or to parameter is NULL then the result is None
When either "from" or "to" is a negative value, it refers to the end of the array, not the beginning. For example, “0” denotes the last position in the array and "-2" denotes the third-to-last position in the array.
If both "from" and "to" exceed the upper limit of the array or fall below the lower limit of the array, the result will be an empty set.
FILTER_ARRAY( <array_expr> , <func>)
Filters the array in expr using func function.
Supported Datatypes, array
Lambda expressions in SQL enable users to efficiently create functions within SQL queries for manipulating array-based data. Specifically designed to handle nested data structures like arrays.
The function iterates through the elements of an array and applies the user-provided transformation or expression.
<func> should be a boolean expression
NOTE - Known limitations:
Aggregate functions are not supported in filter functions.
Lambda expressions are not supported in 'IN' clause.
UNNEST( <array_expr> )
UNNEST transforms an ARRAY into a table, generating a separate row for each element within the ARRAY.
Supported Datatypes, array
Last updated