Hologres：數組（ARRAY）函數

描述：使用提供的分隔字元串連數組元素，如果數組中有NULL元素，可以自訂如何表示數組中的NULL值。

array_to_string(anyarray, text[, text])

傳回值說明

返回TEXT類型。

樣本

--返回結果：1,2,3
SELECT array_to_string(ARRAY[1, 2, 3], ',')

描述：將一列的多行資料彙總成一個數組。

• 方式一：

  array_agg(anyelement)

  使用說明：

  • 僅HologresV1.3及以上版本支援DECIMAL、DATE、TIMESTAMP、TIMESTAMPTZ資料類型。

    說明

    若您Hologres執行個體的版本較低，請您使用執行個體升級或加入HologresDingTalk交流群反饋，詳情請參見如何擷取更多的線上支援？。

  • 暫不支援JSON、JSONB、TIMETZ、INTERVAL、INET、OID、UUID和ARRAY類型。

• 方式二：

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

  使用說明：

  僅HologresV1.3及以上版本支援FILTER過濾。

  說明

  若您Hologres執行個體的版本較低，請您使用執行個體升級或加入HologresDingTalk交流群反饋，詳情請參見如何擷取更多的線上支援？。

傳回值說明

返回ARRAY類型。

樣本

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

  返回結果如下。

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

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

  返回結果如下。

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

描述：添加元素至數組的尾部。

array_append(anyarray, anyelement)

傳回值說明

返回ARRAY類型。

樣本

--返回結果：{1,2,3}
SELECT array_append(ARRAY[1,2], 3);

描述：串連兩個數組。

array_cat(anyarray,anyarray)

傳回值說明

返回ARRAY類型。

樣本

--返回結果：{1,2,3,4,5}
SELECT array_cat(ARRAY[1,2,3], ARRAY[4,5]);

描述：擷取數組的維度數。

array_ndims(anyarray)

傳回值說明

返回INT類型。

樣本

--返回結果：2
SELECT array_ndims(ARRAY[[1,2,3], [4,5,6]]);

描述：擷取數組文本類型的維度數。

array_dims(anyarray)

傳回值說明

返回TEXT類型。

樣本

--返回結果：[1:2][1:3]
SELECT array_dims(ARRAY[[1,2,3], [4,5,6]]);

描述：擷取請求的數組維度長度。

array_length(anyarray, int)

傳回值說明

返回INT類型。

樣本

--返回結果：3
SELECT array_length(ARRAY[1,2,3], 1);

描述：擷取請求的數組維度下限。

array_lower(anyarray, int)	

傳回值說明

返回INT類型。

樣本

--返回結果：0
SELECT array_lower('[0:2]={1,2,3}'::int[], 1);

描述：擷取數組（數組必須是一維的）中指定元素的下標。

array_positions(anyarray, anyelement)

傳回值說明

返回ARRAY類型。

樣本

--返回結果：{1,2,4}
SELECT array_positions(ARRAY['A','A','B','A'], 'A');

描述：添加元素至數組的首位。

array_prepend(anyelement, anyarray)

傳回值說明

返回ARRAY類型。

樣本

--返回結果：{1,2,3}
SELECT array_prepend(1, ARRAY[2,3]);

描述：從數組中刪除所有等於指定值的元素（數組必須是一維的）。

array_remove(anyarray, anyelement)

傳回值說明

返回ARRAY類型。

樣本

--返回結果：{1,3}
SELECT array_remove(ARRAY[1,2,3,2], 2);

描述：對數組元素進行排序。

array_sort(anyarray)

使用說明：

• Hologres從V1.1.46版本開始支援TEXT類型數組，且TEXT數組將會被轉為INT8數組進行排序，返回排序後的TEXT數組。

• Hologres從V1.3.18版本開始支援INT4、INT8、FLOAT4、FLOAT8、BOOLEAN類型數組，且對TEXT類型數組按字典序排序。

傳回值說明

返回ARRAY類型。

樣本

--返回結果：{1,1,2,3}
SELECT array_sort(ARRAY[1,3,2,1]);

描述：擷取請求的數組維度上限。

array_upper(anyarray, int)

傳回值說明

返回INT類型。

樣本

--返回結果：4
SELECT array_upper(ARRAY[1,8,3,7], 1);

描述：將數組的每個元素擴充為單獨行。

unnest(anyarray)

傳回值說明

返回TEXT類型。

樣本

SELECT unnest(ARRAY[1,2]);

返回結果如下。

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

描述：擷取數組中所有資料的最大值，計算過程中會跳過NULL值。

array_max(array)

使用說明：

僅Hologres V1.3.19及以上版本支援該函數。

傳回值說明

返回INT類型。

樣本

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
------------------+-----------
 \N               |\N
 {-2,0,-3,-12,-7} |         0
(2 rows)

描述：擷取數組中所有資料的最小值。

array_min(array)

使用說明：

僅Hologres V1.3.19及以上版本支援該函數。

傳回值說明

返回INT類型。

樣本

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
------------------------------+-----------
 \N                           |\N
 {hello,holo,blackhole,array} | array
(2 rows)

描述：確認數組是否包含指定值。

array_contains(array, target_value)

使用說明：

僅Hologres V1.3.19及以上版本支援該函數。

傳回值說明

返回BOOLEAN類型，如果數組若包含指定值，則返回true，反之返回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)

使用說明：

僅Hologres V1.3.19及以上版本支援該函數。

傳回值說明

返回ARRAY類型。

樣本

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)

使用說明：

僅Hologres V1.3.19及以上版本支援該函數。

傳回值說明

返回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)

使用說明：

僅Hologres V1.3.19及以上版本支援該函數。

傳回值說明

返回ARRAY類型。

樣本

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
--------------+------------+-----------------
 \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)

描述：對字串按Regex進行匹配，如果存在則會在結果數組中表示出來。

regexp_match(<str> text, <pattern> text) 

參數說明

• str：必填，待匹配的字串。

• pattern：必填，Regex。

傳回值說明

返回ARRAY類型。

樣本

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

返回結果如下。

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

描述：字串分割函數，按Regex對字串進行分割並轉換成數組。

regexp_split_to_array(<str> text, <pattern> text)

參數說明

• str：必填，待分割的字串。

• pattern：必填，定義了如何分割字串的Regex。Regex中的特殊字元和構造將用於識別字串中需要作為分隔點的位置。

傳回值說明

返回ARRAY類型。

樣本

CREATE TABLE interests_test (
    name text,
    intrests text
);

INSERT INTO interests_test
    VALUES ('張三', '唱歌,跳舞'), ('李四', '踢球,跑步,畫畫'), ('王五', '插花,書法,彈琴,睡覺');

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

返回結果如下。

name | regexp_split_to_array
----------------------------
張三 | {唱歌,跳舞}
李四 | {踢球,跑步,畫畫}
王五 | {插花,書法,彈琴,睡覺}