)
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( \, 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
```sql
> 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( \, 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
```sql
> 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\_expr3] )
Appends given arrays .
Supported Datatypes, array
```sql
> 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( \, \ )
If the specified value is present in the designated ARRAY, the function returns TRUE.
Supported Datatypes, array
```sql
> SELECT ARRAY_CONTAINS(array['hello', 'hi'],'hello');
TRUE
> SELECT ARRAY_CONTAINS(array['hello', 'hi'],'bye');
FALSE
```
#### ARRAY\_JOIN( \, \ )
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
```sql
> 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( \, \, \ )
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.
```sql
> 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( \ , \)
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
```sql
> 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( \ )
UNNEST transforms an ARRAY into a table, generating a separate row for each element within the ARRAY.
Supported Datatypes, array
```sql
> 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
```