Hologres:Array functions

Operator

Return type

Description

Example

Result

@>

BOOLEAN

Checks whether Array A contains Array B.

SELECT ARRAY[1,2,3] @> ARRAY[1,2];

t

<@

BOOLEAN

Checks whether Array A is contained by Array B.

SELECT ARRAY[1,2,3] <@ ARRAY[1,2];

f

&&

BOOLEAN

Checks whether two arrays have same elements.

SELECT ARRAY[1,2,3] && ARRAY[1,2];

t

Function

Return type

Description

Example

Result

array_to_string(anyarray,text[,text])

TEXT

Concatenates array elements with a specified delimiter and an optional empty string.

array_to_string(ARRAY[1, 2, 3], ',')

1,2,3

array_agg(anyelement)

ARRAY

Concatenates the values of an expression into an array.

• Hologres V1.3 and later support DECIMAL, DATE, TIMESTAMP, and TIMESTAMPTZ data types.

• Hologres does not support JSON, JSONB, TIMETZ, INTERVAL, INET, OID, and UUID data types.

• Hologres does not support the ARRAY data type.

CREATE TABLE test_array_agg_int (
    c1 int
);

INSERT INTO test_array_agg_int
    VALUES (1), (2);

SELECT
    array_agg (c1)
FROM
    test_array_agg_int;

 array_agg
-----------
 {1,2}
(1 row)

array_agg (expression[ order_by_clause ] ) [ FILTER ( WHERE filter_clause ) ]

ARRAY

Concatenates the values of an expression into an array. Hologres V1.3 and later support FILTER statements.

CREATE TABLE test_array_agg_int (
    c1 int
);

INSERT INTO test_array_agg_int
    VALUES (1), (2);

SELECT
    array_agg (c1) filter (where c1 >1)
FROM
    test_array_agg_int;

 array_agg
-----------
 {2}

array_append(anyarray, anyelement)

ARRAY

Appends an element to the end of an array.

array_append(ARRAY[1,2], 3)

{1,2,3}

array_cat(anyarray,anyarray)

ARRAY

Concatenates two arrays.

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

{1,2,3,4,5}

array_ndims(anyarray)

ARRAY

Returns the number of dimensions of an array.

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

2

array_dims(anyarray)

ARRAY

Returns the dimensions of an array in the text format.

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

[1:2][1:3]

array_length(anyarray, int)

ARRAY

Returns the length of the dimension of the requested array.

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

3

array_lower(anyarray, int)

ARRAY

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

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

0

array_positions(anyarray, anyelement)

ARRAY

Returns an array of the location of the second argument in the first argument. The array returned must be one-dimensional.

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

{1,2,4}

array_prepend(anyelement, anyarray)

ARRAY

Prepends an element to the start of an array.

array_prepend(1, ARRAY[2,3])

{1,2,3}

array_remove(anyarray, anyelement)

ARRAY

Removes all elements that are equal to the specified value from an array.

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

{1,3}

array_sort(anyarray)

ARRAY

Sorts the elements in an array.

• Hologres V1.1.46 and later support arrays of the TEXT data type. If you specify an array of the TEXT data type, this function converts the elements of the TEXT data type into the elements of the INT8 data type, sorts the elements of the INT8 data type, and returns the sorted elements.

• Hologres V1.3.18 and later support arrays of the INT4, INT8, FLOAT4, FLOAT8, and BOOLEAN data types. This function sorts the elements in an array of the TEXT data type in alphabetical order.

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

{1,1,2,3}

array_upper(anyarray, int)

INT

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

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

4

unnest(anyarray)

TEXT

Returns array elements that are displayed in multiple rows.

unnest(ARRAY[1,2])

1

2

(2 rows)

array_max(array)

INT

Returns the maximum value among all data of an array. NULL values are not used for calculation.

CREATE TABLE test_array_max_int (
    c1 int[]
);

INSERT INTO test_array_max_int
    VALUES (NULL), (ARRAY[-2, NULL, -3, -12, -7]);

SELECT
    c1,
    array_max (c1)
FROM
    test_array_max_int;

        c1        | array_max
------------------+-----------
                  |
 {-2,0,-3,-12,-7} |         0
(2 rows)

array_min(array)

INT

Returns the minimum value among all data of an array.

CREATE TABLE test_array_min_text (
    c1 text[]
);

INSERT INTO test_array_min_text
    VALUES (NULL), (ARRAY['hello', 'holo', 'blackhole', 'array']);

SELECT
    c1,
    array_min (c1)
FROM
    test_array_min_text;

              c1              | array_min
------------------------------+-----------
                              |
 {hello,holo,blackhole,array} | array
(2 rows)

array_contains(array, target_value)

BOOLEAN

If an array contains the value of the target_value argument, this function returns true. Otherwise, this function returns false.

CREATE TABLE test_array_contains_text (
    c1 text[],
    c2 text
);

INSERT INTO test_array_contains_text
    VALUES (ARRAY[NULL, 'cs', 'holo', 'sql', 'a', NULL, ''], 'holo')
                , (ARRAY['holo', 'array', 'FE', 'l', NULL, ''], 'function');

SELECT
    c1,
    c2,
    array_contains (c1, c2)
FROM
    test_array_contains_text;

            c1            |    c2    | array_contains
--------------------------+----------+----------------
 {holo,array,FE,l,"",""}  | function | f
 {"",cs,holo,sql,a,"",""} | holo     | t
(2 rows)

array_except(array1, array2)

ARRAY

Returns an array whose elements belong to array1 instead of array2.

CREATE TABLE test_array_except_text (
    c1 text[],
    c2 text[]
);

INSERT INTO test_array_except_text
    VALUES (ARRAY['o', 'y', 'l', 'l', NULL, ''], NULL), (ARRAY['holo', 'hello', 'hello', 'SQL', '', 'blackhole'], ARRAY['holo', 'SQL', NULL, 'kk']);

SELECT
    c1,
    c2,
    array_except (c1, c2)
FROM
    test_array_except_text;

                 c1                  |        c2        |   array_except
-------------------------------------+------------------+-------------------
 {o,y,l,l,"",""}                     |                  | {o,l,y,""}
 {holo,hello,hello,SQL,"",blackhole} | {holo,SQL,"",kk} | {blackhole,hello}
(2 rows)

array_distinct(array)

ARRAY

Removes duplicate values from an array.

CREATE TABLE test_array_distinct_text (
    c1 text[]
);

INSERT INTO test_array_distinct_text
    VALUES (ARRAY['holo', 'hello', 'holo', 'SQL', 'SQL']), (ARRAY[]::text[]);

SELECT
    c1,
    array_distinct (c1)
FROM
    test_array_distinct_text;

            c1             |  array_distinct
---------------------------+------------------
 {holo,hello,holo,SQL,SQL} | {SQL,hello,holo}
 {}                        | {NULL}
(2 rows)

array_union(array1, array2)

ARRAY

Returns an array whose elements are the values in the union of array1 and array2. The returned array does not include duplicate values.

CREATE TABLE test_array_union_int (
    c1 int[],
    c2 int[]
);

INSERT INTO test_array_union_int
    VALUES (NULL, ARRAY[2, -3, 2, 7]), (ARRAY[2, 7, -3, 2, 7], ARRAY[12, 9, 8, 7]);

SELECT
    c1,
    c2,
    array_union (c1, c2)
FROM
    test_array_union_int;

      c1      |     c2     |   array_union
--------------+------------+-----------------
              | {2,-3,2,7} | {2,7,-3}
 {2,7,-3,2,7} | {12,9,8,7} | {9,2,7,8,12,-3}
(2 rows)