Hologres:Array functions

Concatenates array elements by using a specific delimiter. If NULL values exist in the array, you can specify how to represent the NULL values.

array_to_string(anyarray, text[, text])

Return value

A value of the TEXT type is returned.

Example

-- Result: 1,2,3
SELECT array_to_string(ARRAY[1, 2, 3], ',')

Aggregates multiple rows of data in a column into an array.

• Method 1

  array_agg(anyelement)

  Usage notes:

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

    Note

    If your Hologres instance is of an earlier version, upgrade the instance or join the Hologres DingTalk group for support. For more information, see Obtain online support for Hologres.

  • The JSON, JSONB, TIMETZ, INTERVAL, INET, OID, UUID, and ARRAY data types are not supported.

• Method 2

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

  Usage notes:

  Only Hologres V1.3 and later support FILTER.

  Note

  If your Hologres instance is of an earlier version, upgrade the instance or join the Hologres DingTalk group for support. For more information, see Obtain online support for Hologres

Return value

A value of the ARRAY type is returned.

Example

• Example 1 (array_agg(anyelement))

  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;

  Result:

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

• Example 2 (FILTER):

  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;

  Result:

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

Appends an element to the end of an array.

array_append(anyarray, anyelement)

Return value

A value of the ARRAY type is returned.

Example

-- Result: 1,2,3
SELECT array_append(ARRAY[1,2], 3);

Concatenates two arrays.

array_cat(anyarray,anyarray)

Return value

A value of the ARRAY type is returned.

Example

-- Result: {1,2,3,4,5}
SELECT array_cat(ARRAY[1,2,3], ARRAY[4,5]);

Returns the number of dimensions of an array.

array_ndims(anyarray)

Return value

A value of the INT type is returned.

Example

-- Result: 2
SELECT array_ndims(ARRAY[[1,2,3], [4,5,6]]);

Returns the text representation of the dimensions of an array.

array_dims(anyarray)

Return value

A value of the TEXT type is returned.

Example

-- Result: [1:2][1:3]
SELECT array_dims(ARRAY[[1,2,3], [4,5,6]]);

Returns the length of the dimension of the requested array.

array_length(anyarray, int)

Return value

A value of the INT type is returned.

Example

-- Result: 3
SELECT array_length(ARRAY[1,2,3], 1);

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

array_lower(anyarray, int)	

Return value

A value of the INT type is returned.

Example

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

Returns the index of a specific element in an array. The array must be one-dimensional.

array_positions(anyarray, anyelement)

Return value

A value of the ARRAY type is returned.

Example

-- Result: {1,2,4}
SELECT array_positions(ARRAY['A','A','B','A'], 'A');

Appends an element to the beginning of an array.

array_prepend(anyelement, anyarray)

Return value

A value of the ARRAY type is returned.

Example

-- Result: 1,2,3
SELECT array_prepend(1, ARRAY[2,3]);

Removes all elements that are equal to the specified value from an array. The array must be one-dimensional.

array_remove(anyarray, anyelement)

Return value

A value of the ARRAY type is returned.

Example

-- Result: {1,3}
SELECT array_remove(ARRAY[1,2,3,2], 2);

Sorts the elements in an array.

array_sort(anyarray)

Usage notes:

• Hologres V1.1.46 and later support TEXT arrays. The TEXT arrays are converted into INT8 arrays for sorting and the sorted TEXT arrays are returned.

• Hologres V1.3.18 and later support arrays of the INT4, INT8, FLOAT4, FLOAT8, and BOOLEAN data types. Arrays of the TEXT data type are sorted in lexicographic order.

Return value

A value of the ARRAY type is returned.

Example

-- Result: {1,1,2,3}
SELECT array_sort(ARRAY[1,3,2,1]);

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

array_upper(anyarray, int)

Return value

A value of the INT type is returned.

Example

-- Result: 4
SELECT array_upper(ARRAY[1,8,3,7], 1);

Returns array elements that are displayed in multiple rows.

unnest(anyarray)

Return value

A value of the TEXT type is returned.

Example

SELECT unnest(ARRAY[1,2]);

Result:

unnest
------
1
2
(2 rows)

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

array_max(array)

Usage notes:

Only Hologres V1.3.19 and later support this function.

Return value

A value of the INT type is returned.

Example

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;

Result:

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

Returns the minimum value among all data of an array.

array_min(array)

Usage notes:

Only Hologres V1.3.19 and later support this function.

Return value

A value of the INT type is returned.

Example

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;

Result:

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

Checks whether an array contains a specific value.

array_contains(array, target_value)

Usage notes:

Only Hologres V1.3.19 and later support this function.

Return value

A value of the BOOLEAN type is returned. If the array contains a specific value, true is returned. Otherwise, false is returned.

Example

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;

Result:

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

Returns an array that does not contain a specific element.

array_except(array1, array2)

Usage notes:

Only Hologres V1.3.19 and later support this function.

Return value

A value of the ARRAY type is returned.

Example

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;

Result:

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

Removes duplicate elements from an array.

array_distinct(array)

Usage notes:

Only Hologres V1.3.19 and later support this function.

Return value

A value of the ARRAY type is returned.

Example

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;

Result:

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

Aggregates two arrays into a new array that does not contain duplicate elements.

array_union(array1, array2)

Usage notes:

Only Hologres V1.3.19 and later support this function.

Return value

A value of the ARRAY type is returned.

Example

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;

Result:

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

Description: Matches the content of a string by using regular expressions. Content fragments that meet the matching conditions are displayed in an array.

REGEXP_MATCH(<str> TEXT, <pattern> TEXT) 

Parameters

• str: required. The string to be matched.

• pattern: required. The regular expression.

Return value

A value of the ARRAY type is returned.

Example

SELECT regexp_match('foobarbequebaz', '(bar)(beque)');

The following result is returned:

regexp_match
------------
{bar,beque}

Description: Splits a string based on a regular expression and returns an array.

REGEXP_SPLIT_TO_ARRAY(<str> TEXT, <pattern> TEXT)

Parameters

• str: required. The string to be split.

• pattern: required. The regular expression based on which the string is split. The string is split based on the special character and construct in the regular expression.

Return value

A value of the ARRAY type is returned.

Example

CREATE TABLE interests_test (
    name text,
    intrests text
);

INSERT INTO interests_test
    VALUES ('Ava', 'singing, dancing'), ('Bob', 'playing football, running, painting'), ('Jack', 'arranging flowers, writing calligraphy, playing the piano, sleeping');

SELECT
    name,
    REGEXP_SPLIT_TO_ARRAY(intrests, ',')
FROM
    interests_test;

The following result is returned:

name | regexp_split_to_array
----------------------------
Ava | {singing, dancing}
Bob | {playing football, running, painting}
Jack | {arranging flowers, writing calligraphy, playing the piano, sleeping}