# 15.17. Array Functions and Operators

## Subscript Operator: []

The `[]` operator is used to access an element of an array and is indexed starting from one:

```SELECT my_array[1] AS first_element
```

## Concatenation Operator: ||

The `||` operator is used to concatenate an array with an array or an element of the same type:

```SELECT ARRAY [1] || ARRAY [2]; -- [1, 2]
SELECT ARRAY [1] || 2; -- [1, 2]
SELECT 2 || ARRAY [1]; -- [2, 1]
```

## Array Functions

`array_distinct`(x) → array

Remove duplicate values from the array `x`.

`array_intersect`(x, y) → array

Returns an array of the elements in the intersection of `x` and `y`, without duplicates.

`array_union`(x, y) → array

Returns an array of the elements in the union of `x` and `y`, without duplicates.

`array_join`(x, delimiter, null_replacement) → varchar

Concatenates the elements of the given array using the delimiter and an optional string to replace nulls.

`array_max`(x) → x

Returns the maximum value of input array.

`array_min`(x) → x

Returns the minimum value of input array.

`array_position`(x, element) → bigint

Returns the position of the first occurrence of the `element` in array `x` (or 0 if not found).

`array_remove`(x, element) → array

Remove all elements that equal `element` from array `x`.

`array_sort`(x) → array

Sorts and returns the array `x`. The elements of `x` must be orderable. Null elements will be placed at the end of the returned array.

`cardinality`(x) → bigint

Returns the cardinality (size) of the array `x`.

`concat`(x, y) → array

Concatenates the arrays `x` and `y`. This function provides the same functionality as the SQL-standard concatenation operator (`||`).

`contains`(x, element) → boolean

Returns true if the array `x` contains the `element`.

`element_at`(array<E>, index) → E

Returns element of `array` at given `index`. If `index` >= 0, this function provides the same functionality as the SQL-standard subscript operator (`[]`). If `index` < 0, `element_at` accesses elements from the last to the first.

`filter`(array, function) → array
`flatten`(x) → array

Flattens an `array(array(T))` to an `array(T)` by concatenating the contained arrays.

`reduce`(array, initialState, inputFunction, outputFunction) → x
`reverse`(x) → array

Returns an array which has the reversed order of array `x`.

`sequence`(start, stop) → array<bigint>

Generate a sequence of integers from `start` to `stop`, incrementing by `1` if `start` is less than or equal to `stop`, otherwise `-1`.

`sequence`(start, stop, step) → array<bigint>

Generate a sequence of integers from `start` to `stop`, incrementing by `step`.

`sequence`(start, stop, step) → array<timestamp>

Generate a sequence of timestamps from `start` to `stop`, incrementing by `step`. The type of `step` can be either `INTERVAL DAY TO SECOND` or `INTERVAL YEAR TO MONTH`.

`shuffle`(x) → array

Generate a random permutation of the given array `x`.

`slice`(x, start, length) → array

Subsets array `x` starting from index `start` (or starting from the end if `start` is negative) with a length of `length`.

`transform`(array, function) → array
`zip`(array1, array2[, ...]) → array<row>

Merges the given arrays, element-wise, into a single array of rows. The M-th element of the N-th argument will be the N-th field of the M-th output element. If the arguments have an uneven length, missing values are filled with `NULL`.

```SELECT zip(ARRAY[1, 2], ARRAY['1b', null, '3b']); -- [ROW(1, '1b'), ROW(2, null), ROW(null, '3b')]
```
`zip_with`(array1, array2, function) → array