Skip to main content

Array Functions and Operators

Extreme DPS provides a range of array functions and operators to help you use and process data of the array type.

Array operators​

Currently, Extreme DPS supports the subscript operator [] and the concatenation operator ||.

[]

Accesses the element of an array at a specified subscript number. The subscript numbers of elements in an array start from 1.

Null is returned in the following scenarios:

  • The specified subscript number is out of bounds.

  • The specified subscript number is negative.

  • The specified subscript number is 0.

Following are some examples:

SELECT array_col[1] FROM (VALUES(ARRAY[11, 22, 33])) as t1(array_col); 
-> 11

SELECT array_col[8] FROM (VALUES(ARRAY[11, 22, 33])) as t1(array_col); 
-> null

SELECT array_col[0] FROM (VALUES(ARRAY[11, 22, 33])) as t1(array_col); 
-> null

SELECT array_col[-1] FROM (VALUES(ARRAY[11, 22, 33])) as t1(array_col); 
-> null

||​

Concatenates two arrays with the same type of elements.

Here are some examples:

SELECT ARRAY[1,2,3] || ARRAY[4,5,6]; 
-> [1, 2, 3, 4, 5, 6]

SELECT 3 || ARRAY[4,5,6]; 
-> [3, 4, 5, 6]

SELECT ARRAY[4,5,6] || 7; 
-> [4, 5, 6, 7]

Array functions​

This section introduces the array functions supported by Extreme DPS.

ARRAY_SORT​

Returns the input array with its elements sorted in ascending order. The elements of the array must be sortable. NULL elements are placed at the end of the array.

Syntax​

ARRAY_SORT(<array>)

Arguments​

<array>: the array of which elements need to be sorted.

Returns​

The same type as the input.

Examples​

SELECT ARRAY_SORT(ARRAY[2, 1, 3]); 
-> {1, 2, 3}

ARRAY_SORT_DESC​

Returns the array with its elements sorted in descending order. The elements of the array must be sortable. NULL elements are placed at the end of the array.

Syntax​

ARRAY_SORT_DESC(<array>)

Arguments​

<array>: the array of which elements need to be sorted.

Returns​

The same type as the input.

Examples​

SELECT ARRAY_SORT_DESC(ARRAY[2, 1, 3]); 
-> {3, 2, 1}

ARRAY_ELEMENT_SUM​

Returns the sum of all non-NULL elements in the array.

Syntax​

ARRAY_ELEMENT_SUM(<array>)

Arguments​

<array>: the array whose elements need to be summed up, where the element type can be smallint, integer, bigint, real, double precision.

Returns​

  • For input type smallint, integer, or bigint, the return type is bigint.

  • For input type real or double precision, the return type is double precision.

Examples​

SELECT ARRAY_ELEMENT_SUM(ARRAY[1, null, 2, 3]); 
-> 6
SELECT ARRAY_ELEMENT_SUM(ARRAY[]::integer array); 
-> 0

CARDINALITY​

Returns the number of elements in an array. If the array is empty, 0 will be returned.

Syntax​

CARDINALITY(<array>)

Arguments​

<array>: the array for which the number of elements is to be counted.

Returns​

An integer value.

Examples​

SELECT CARDINALITY(ARRAY[ARRAY[1,2],ARRAY[3,4]]);  
-> 4
SELECT CARDINALITY(ARRAY[11, 22, 33]); 
-> 3

ARRAYS_OVERLAP​

Checks whether a non-NULL element intersection between two arrays exists.

Syntax​

ARRAYS_OVERLAP(<array1>, <array2>)

Arguments​

  • <array1>

    One array to compare.

  • <array2>

    The other array to compare.

Returns​

A boolean value:

  • If a non-null element intersection between the two arrays exists, true will be returned.

  • If no non-null element intersection between the two arrays exists, false will be returned.

  • If no non-null element intersection exists and one or both of the arrays contain null elements, null will be returned.

Examples​

SELECT ARRAYS_OVERLAP(ARRAY[11, 22, 33], ARRAY[22]); 
-> true
SELECT ARRAYS_OVERLAP(ARRAY[11, 22, 33], ARRAY[22, 44]); 
-> true
SELECT ARRAYS_OVERLAP(ARRAY[11, 22, 33], ARRAY[44, 55]); 
-> false
SELECT ARRAYS_OVERLAP(ARRAY[11, 22, null, 33], ARRAY[33, 44]); 
-> true
SELECT ARRAYS_OVERLAP(ARRAY[11, 22, null, 33], ARRAY[44, 55]); 
-> null

CONTAINS​

Checks whether an array contains a specified element.

Syntax​

CONTAINS(<array>, <element>)

Arguments​

  • <array>

    The array to check.

  • <element>

    The specified element.

Returns​

A boolean value:

  • If the array contains the specified elemen and the specified element is not null, true will be returned.

  • If the array does not contain the specified element and the specified element is not null, false will be returned.

  • If the specified element is null, null will be returned.

Examples​

SELECT CONTAINS(ARRAY[11, 22, 33], 11); 
-> true
SELECT CONTAINS(ARRAY[11, 22, 33, null], 100); 
-> false
SELECT CONTAINS(ARRAY[11, 22, 33], null); 
-> null
SELECT CONTAINS(ARRAY[11, 22, 33, null], null); 
-> null