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

> select a[1] from (select array[1,2,3] as a)
1

ELEMENT_AT ( <array_expr>, index )

The element_at function returns element of array at a given index. The index starts with 1

> select element_at(array[1,2,3,4,5],2) 
2

ARRAY_POSITION( <expr>, <array_expr> )

Returns the position of the passed argument in an array

Supported datatype: INT/DOUBLE/VARCHAR/BOOLEAN

> select array_position(1.9,array[1,2,3,1.9])
4

SIZE( <array_expr> )

Returns the size of the input array.

> select size(array[1,2,3,4,5]) 
5

ARRAY_TO_STRING( distinct <expr> )

Returns an ARRAY of distinct values

> select array_to_string(array[1,2,3], '+')
'1+2+3'

Limitations - Complex datatype are not supported

ARRAY_AGG( [distinct] <array_expr> )

Concatenates the elements of specified array expression with the provided delimiter.

>   with cte as(
    select 1 as id, 'a' as c1
    union all
    select 1 as id, 'a' as c1
    union all
    select 1 as id, 'b' as c1
    union all
    select 2 as id, 'b' as c1
    union all
    select 2 as id, 'b' as c1
)
select id,array_agg(distinct c1 ) 
from cte
group by 1
order by 1

1, [ "a", "b" ]
2, [ "b" ]

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

> SELECT ARRAY_APPEND(ARRAY[1, 2, 3, 4, 5, 6], 7)
[1,   2,   3,   4,   5,   6,   7 ]

> SELECT ARRAY_APPEND(ARRAY['a', 'b', 'c'], 'd')
["a",   "b",   "c",   "d" ]

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

> SELECT ARRAY_PREPEND(ARRAY['a','b','c','d'],'e'); 
[ e, a, b, c, d]    

> SELECT ARRAY_PREPEND(ARRAY[1, 2, 3, 4],5);
[5, 1, 2, 3, 4]

ARRAY_CONCAT( <array_expr1>, <array_expr2>, [array_expr3] )

Appends given arrays .

Supported Datatypes, array

> SELECT ARRAY_CONCAT(array[1,2,3],array[10,20])
[ 1, 2, 3, 10, 20]    

> SELECT ARRAY_CONCAT(array['a','b','c'],array['d','e'])
[a, b, c, d, e]

ARRAY_CONTAINS( <array_expr>, <value> )

If the specified value is present in the designated ARRAY, the function returns TRUE.

Supported Datatypes, array

> SELECT ARRAY_CONTAINS(array['hello', 'hi'],'hello');
TRUE    

> SELECT ARRAY_CONTAINS(array['hello', 'hi'],'bye');
FALSE

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

> select array_join(array['Hello',NULL,'Welcome',NULL,'To',NULL,'E6data'],'; ')
Hello; Welcome; To; E6data  

>select array_join(array['Hello',NULL,'Welcome',NULL,'To',NULL,'E6data'],'; ', '@')
Hello; @; Welcome; @; To; @; E6data

ARRAY_SLICE( <array_expr>, <from_index>, <to> )

Returns an array constructed from a specified subset of elements of the input array.

Supported Datatypes, array

1. The output result comprises elements from the from index upto to parameter but excluding the element identified by the to parameter.

2. Array starts with index 1.

3. If array, from index or to parameter is NULL then the result is None

4. 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.

5. 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.

> select array_slice(array[0,1,2,3,4,5,6], 1, 3)
[   0,   1 ]
> select array_slice(NULL, 3, 4)
None
> select array_slice(array[0,1,2,3,4,5,6], NULL, 3)
None
> select array_slice(array[0,1,2,3,4,5,6], 3,NULL)
None
> select array_slice(array[0,1,2,3,4,5,6], 1, -1)
[   0,   1,   2,   3,   4 ]

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

> select filter_array(array[10, null, 6, 7, null], x -> x is not null);
10, 6, 7

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

> SELECT * FROM UNNEST(ARRAY[10,20,30]) as numbers
|10
|20
|30

> WITH Sequences AS
  (SELECT 1 AS id, array[0, 1, 1, 2, 3, 5] AS some_numbers
   UNION ALL SELECT 2 AS id, array[2, 4, 8, 16, 32] AS some_numbers
   UNION ALL SELECT 3 AS id, array[5, 10] AS some_numbers)
SELECT id, flattened_numbers
FROM Sequences
CROSS JOIN UNNEST(Sequences.some_numbers) AS flattened_numbers
order by 1,2;
|1    |0
|1    |1
|1    |1
|1    |2
|1    |3
|1    |5
|2    |2
|2    |4
|2    |8
|2    |16
|2    |32
|3    |5
|3    |10