Hologres:Array functions

Description: Concatenates array elements using a specified separator. You can specify a string to represent NULL values in the array.

array_to_string(anyarray, text[, text])

Parameter description

• anyarray: The array whose elements you want to concatenate.

• text (second parameter): The string to use as a separator.

• text (third parameter, optional): The string used to represent NULL values. If this parameter is not specified, NULL values are ignored.

Return value description

Returns a TEXT value.

Example

-- The result is 1,2,3
SELECT array_to_string(ARRAY[1, 2, 3], ',')

Description: Aggregates data from multiple rows in a column into an array.

• Method 1:

  array_agg(anyelement)

  Parameter description

  • anyelement: The column or expression to aggregate.

  Usage notes:

  • The DECIMAL, DATE, TIMESTAMP, and TIMESTAMPTZ data types are supported only in Hologres V1.3 and later.

    Note

    If your Hologres instance is an earlier version, upgrade your instance or join the Hologres DingTalk group for feedback. For more information, see Instance upgradation or How do I get more online support?.

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

• Method 2:

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

  Parameter description

  • expression: The expression to aggregate.

  • order_by_clause (optional): The ORDER BY clause that specifies the sort order for the aggregation.

  • filter_clause (optional): The filter condition for the FILTER clause. Only rows that meet the condition are included in the aggregation.

  Usage notes:

  The FILTER clause is supported only in Hologres V1.3 and later.

  Note

  If your Hologres instance is an earlier version, upgrade your instance or join the Hologres DingTalk group for feedback. For more information, see Instance upgradation or How do I get more online support?.

Return value description

Returns an ARRAY value.

Examples

• 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;

  The following result is returned.

   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;

  The following result is returned.

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

Description: Appends an element to the end of an array.

array_append(anyarray, anyelement)

Parameter description

• anyarray: The source array.

• anyelement: The element to append to the end of the array.

Return value description

Returns an ARRAY value.

Example

-- The result is {1,2,3}
SELECT array_append(ARRAY[1,2], 3);

Description: Concatenates two arrays.

array_cat(anyarray,anyarray)

Parameter description

• anyarray (first parameter): The first array.

• anyarray (second parameter): The second array.

Return value description

Returns an ARRAY value.

Example

-- The result is {1,2,3,4,5}
SELECT array_cat(ARRAY[1,2,3], ARRAY[4,5]);

Description: Returns the number of dimensions of an array.

array_ndims(anyarray)

Parameter description

• anyarray: The array to query.

Return value description

Returns an INT value.

Example

-- The result is 2
SELECT array_ndims(ARRAY[[1,2,3], [4,5,6]]);

Description: Retrieves the number of dimensions of the text array type.

array_dims(anyarray)

Parameter description

• anyarray: The array to query.

Return value description

Returns a TEXT value.

Example

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

Description: Returns the length of a specified array dimension.

array_length(anyarray, int)

Parameter description

• anyarray: The array to query.

• int: The dimension whose length you want to obtain. Dimensions are numbered starting from 1.

Return value description

Returns an INT value.

Example

-- The result is 3
SELECT array_length(ARRAY[1,2,3], 1);

Description: Returns the lower bound of a specified array dimension.

array_lower(anyarray, int)	

Parameter description

• anyarray: The array to query.

• int: The dimension whose lower bound you want to obtain. Dimensions are numbered starting from 1.

Return value description

Returns an INT value.

Example

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

Description: Returns the subscripts of a specified element in a one-dimensional array.

array_positions(anyarray, anyelement)

Parameter description

• anyarray: The one-dimensional array to query.

• anyelement: The element whose subscripts you want to find.

Return value description

Returns an ARRAY value.

Example

-- The result is {1,2,4}
SELECT array_positions(ARRAY['A','A','B','A'], 'A');

Description: Adds an element to the beginning of an array.

array_prepend(anyelement, anyarray)

Parameter description

• anyelement: The element to add to the beginning of the array.

• anyarray: The source array.

Return value description

Returns an ARRAY value.

Example

-- The result is {1,2,3}
SELECT array_prepend(1, ARRAY[2,3]);

Description: Removes all elements equal to a specified value from a one-dimensional array.

array_remove(anyarray, anyelement)

Parameter description

• anyarray: The one-dimensional array to process.

• anyelement: The value to remove from the array. All elements equal to this value are removed.

Return value description

Returns an ARRAY value.

Example

-- The result is {1,3}
SELECT array_remove(ARRAY[1,2,3,2], 2);

Description: Sorts the elements of an array.

array_sort(anyarray)

Parameter description

• anyarray: The array to sort.

Usage notes:

• Hologres V1.1.46 and later support TEXT arrays. TEXT arrays are converted to INT8 arrays for sorting, and the sorted TEXT array is returned.

• Hologres V1.3.18 and later support INT4, INT8, FLOAT4, FLOAT8, and BOOLEAN arrays. TEXT arrays are sorted in lexicographic order.

Return value description

Returns an ARRAY value.

Example

-- The result is {1,1,2,3}
SELECT array_sort(ARRAY[1,3,2,1]);

Description: Returns the upper bound of a specified array dimension.

array_upper(anyarray, int)

Parameter description

• anyarray: The array to query.

• int: The dimension whose upper bound you want to obtain. Dimensions are numbered starting from 1.

Return value description

Returns an INT value.

Example

-- The result is 4
SELECT array_upper(ARRAY[1,8,3,7], 1);

Description: Expands each element of an array into a separate row.

unnest(anyarray)

Parameter description

• anyarray: The array to expand.

Return value description

Returns a TEXT value.

Example

SELECT unnest(ARRAY[1,2]);

The following result is returned.

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

Description: Returns the maximum value of all elements in an array. NULL values are skipped during the calculation.

array_max(array)

Parameter description

• array: The array to calculate. NULL values are skipped during the calculation.

Usage notes:

This function is supported only in Hologres V1.3.19 and later.

Return value description

Returns an INT value.

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;

The following result is returned.

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

Description: Returns the minimum value of all elements in an array.

array_min(array)

Parameter description

• array: The array to calculate.

Usage notes:

This function is supported only in Hologres V1.3.19 and later.

Return value description

Returns an INT value.

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;

The following result is returned.

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

Description: Checks whether an array contains a specified value.

array_contains(array, target_value)

Parameter description

• array: The array to check.

• target_value: The target value to check for in the array.

Usage notes:

This function is supported only in Hologres V1.3.19 and later.

Return value description

Returns a BOOLEAN value. If the array contains the specified value, the function returns true. Otherwise, it returns false.

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;

The following result is returned.

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

Description: Returns an array without the specified array element.

array_except(array1, array2)

Parameter description

• array1: The source array.

• array2: An array of elements to exclude from array1. The result is an array of elements that are in array1 but not in array2.

Usage notes:

This function is supported only in Hologres V1.3.19 and later.

Return value description

Returns an ARRAY value.

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;

The following result is returned.

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

Description: Removes duplicate elements from an array.

array_distinct(array)

Parameter description

• array: The array from which to remove duplicates.

Usage notes:

This function is supported only in Hologres V1.3.19 and later.

Return value description

Returns an ARRAY value.

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;

The following result is returned.

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

Description: Merges two arrays into a new array and removes duplicate elements.

array_union(array1, array2)

Parameter description

• array1: The first array.

• array2: The second array. Duplicates are removed after the two arrays are merged.

Usage notes:

This function is supported only in Hologres V1.3.19 and later.

Return value description

Returns an ARRAY value.

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;

The following result is returned.

      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}