AnalyticDB:Array functions and operators

Operator

Description

Example

Sample result

=

Checks whether two arrays are equal.

ARRAY[1.1,2.1,3.1]::int[] = ARRAY[1,2,3]

t

<>

Checks whether two arrays are not equal.

ARRAY[1,2,3] <> ARRAY[1,2,4]

t

<

Checks whether the array to the left of the operator is less than the array to the right of the operator.

ARRAY[1,2,3] < ARRAY[1,2,4]

t

>

Checks whether the array to the left of the operator is greater than the array to the right of the operator.

ARRAY[1,4,3] > ARRAY[1,2,4]

t

<=

Checks whether the array to the left of the operator is less than or equal to the array to the right of the operator.

ARRAY[1,2,3] <= ARRAY[1,2,3]

t

>=

Checks whether the array to the left of the operator is greater than or equal to the array to the right of the operator.

ARRAY[1,4,3] >= ARRAY[1,4,3]

t

@>

Checks whether the array to the left of the operator contains all elements of the array to the right of the operator.

ARRAY[1,4,3] @> ARRAY[3,1,3]

t

<@

Checks whether all elements of the array to the left of the operator are contained in the array to the right of the operator.

ARRAY[2,2,7] <@ ARRAY[1,7,4,2,6]

t

&&

Checks whether two arrays overlap (have elements in common).

ARRAY[1,4,3] && ARRAY[2,1]

t

||

Concatenates two arrays.

ARRAY[1,2,3] || ARRAY[4,5,6]

{1,2,3,4,5,6}

||

Concatenates a one-dimensional array and a multidimensional array.

ARRAY[1,2,3] || ARRAY[[4,5,6],[7,8,9]]

{{1,2,3},{4,5,6},{7,8,9}}

||

Sequentially concatenates an element and an array.

3 || ARRAY[4,5,6]

{3,4,5,6}

||

Sequentially concatenates an array and an element.

ARRAY[4,5,6] || 7

{4,5,6,7}

Function

Data type of the return value

Description

Example

Sample result

array_append(any array,any element)

any array

Appends an element to the end of an array.

array_append(ARRAY[1,2], 3)

{1,2,3}

array_cat(any array,any array)

any array

Concatenates two arrays.

array_cat(ARRAY[1,2,3], ARRAY[4,5])

{1,2,3,4,5}

array_ndims(any array)

int

Returns the number of dimensions of an array.

array_ndims(ARRAY[[1,2,3], [4,5,6]])

2

array_dims(any array)

text

Returns the text representation of the dimensions of an array.

array_dims(ARRAY[[1,2,3], [4,5,6]])

[1:2][1:3]

array_fill(any element,int[], [,int[]])

any array

Returns an array that is initialized based on the specified value and dimensions and has an optional lower bound (starting index) other than 1.

array_fill(7, ARRAY[3], ARRAY[2])

[2:4]={7,7,7}

array_length(any array,int)

int

Returns the length of the dimension of the requested array.

array_length(array[1,2,3], 1)

3

array_lower(any array,int)

int

Returns the lower bound of the dimension of the requested array.

array_lower('[0:2]={1,2,3}'::int[], 1)

0

array_position(any array,any element[,int])

int

Returns the subscript of the first occurrence of the second argument in the array specified by the first argument, starting at the first element of the array or the position specified by the third argument. This function is supported only for one-dimensional arrays.

array_position(ARRAY['sun','mon','tue','wed','thu','fri','sat'], 'mon')

2

array_positions(any array,any element)

int[]

Returns an array of subscripts of all occurrences of the second argument in the array specified by the first argument. This function is supported only for one-dimensional arrays.

array_positions(ARRAY['A','A','B','A'], 'A')

{1,2,4}

array_prepend(any element,any array)

any array

Appends an element to the beginning of an array.

array_prepend(1, ARRAY[2,3])

{1,2,3}

array_remove(any array,any element)

any array

Removes all elements that are equal to a specific value from an array. This function is supported only for one-dimensional arrays.

array_remove(ARRAY[1,2,3,2], 2)

{1,3}

array_replace(any array,any element,any element)

any array

Replaces the array elements that are equal to a specific value with a new value.

array_replace(ARRAY[1,2,5,4], 5, 3)

{1,2,3,4}

array_to_string(any array,text[,text])

text

Concatenates array elements by using the specified delimiter and an optional null string.

array_to_string(ARRAY[1, 2, 3, NULL, 5], ',', '*')

1,2,3,*,5

array_upper(any array,int)

int

Returns the upper bound of the dimension of the requested array.

array_upper(ARRAY[1,8,3,7], 1)

4

cardinality(any array)

int

Returns the total number of elements in an array. If an array is empty, this function returns 0.

cardinality(ARRAY[[1,2],[3,4]])

4

string_to_array(text,text[,text])

text[]

Splits a string into array elements by using the specified delimiter and an optional null string.

string_to_array('xx~^~yy~^~zz', '~^~', 'yy')

{xx,NULL,zz}

unnest(any array)

set of any element

Expands an array into a set of rows.

unnest(ARRAY[1,2])

1
2

(2 lines)

unnest(any array,any array[, ...])

set of any element, any element [, ...]

Expands multiple arrays into a set of rows. The arrays can be of different data types. This function takes effect only when included in the FROM clause.

unnest(ARRAY[1,2],ARRAY['foo','bar','baz'])

1    foo
2    bar
NULL baz

(3 lines)