Array Functions
Array functions perform basic operations on input array data, such as adding elements, searching for elements, converting arrays, and returning the processed array data.
Table 1 lists the array functions supported by DWS.
| Function | Description | Example | ||
|---|---|---|---|---|
| Append an element to the end of an array. |
| |||
| Append an element to the beginning of an array. |
| |||
| Concatenate two arrays. |
| |||
| Return the number of dimensions of the array. |
| |||
| Return a text representation of array's dimensions. |
| |||
| Return the length of the requested array dimension. |
| |||
| Return lower bound of the requested array dimension. |
| |||
| Return upper bound of the requested array dimension. |
| |||
| Convert an array into a string. |
| |||
| Convert a string into an array. |
| |||
| Convert a multidimensional array to a one-dimensional array. |
| |||
| Return the array after deduplication. NULL values are not counted. |
| |||
| Return the number of elements in an array without duplicate values. |
| |||
| Return an array sorted in ascending order. |
| |||
| Return an array sorted in descending order. |
| |||
| Expand an array to a set of rows. |
| |||
| Search for the last array index that is less than or equal to the target parameter n from the input integer array. |
| |||
| Separate strings by a delimiter and return an array. |
|
array_append(anyarray, anyelement)
Description: Appends an element to the end of an array, and only supports dimension-1 arrays.
Return type: anyarray
Example: Append element 3 to the end of the array [1,2].
1 2 3 4 5 | SELECT array_append(ARRAY[1,2], 3) AS RESULT; result --------- {1,2,3} (1 row) |
array_prepend(anyelement, anyarray)
Description: Appends an element to the beginning of an array and supports only one-dimensional arrays.
Return type: anyarray
Example: Append element 1 to the beginning of the array [2,3].
1 2 3 4 5 | SELECT array_prepend(1, ARRAY[2,3]) AS RESULT; result --------- {1,2,3} (1 row) |
array_cat(anyarray, anyarray)
Description: Concatenates two arrays, and supports multi-dimensional arrays.
Return type: anyarray
Example: Concatenate the array [1,2,3] with the array [4,5].
1 2 3 4 5 | SELECT array_cat(ARRAY[1,2,3], ARRAY[4,5]) AS RESULT; result ------------- {1,2,3,4,5} (1 row) |
Concatenate the two-dimensional array [[1,2],[4,5]] with the one-dimensional array [6,7].
1 2 3 4 5 | SELECT array_cat(ARRAY[[1,2],[4,5]], ARRAY[6,7]) AS RESULT; result --------------------- {{1,2},{4,5},{6,7}} (1 row) |
array_ndims(anyarray)
Description: Returns the number of dimensions of the array.
Return type: int
Example: View the number of dimensions of the array [[1,2,3], [4,5,6]].
1 2 3 4 5 | SELECT array_ndims(ARRAY[[1,2,3], [4,5,6]]) AS RESULT; result -------- 2 (1 row) |
array_dims(anyarray)
Description: Returns a text representation of array's dimensions.
Return type: text
Example: Obtain the dimensions of an array. The returned result indicates that the array is a two-dimensional array with 2 rows and 3 columns.
1 2 3 4 5 | SELECT array_dims(ARRAY[[1,2,3], [4,5,6]]) AS RESULT; result ------------ [1:2][1:3] (1 row) |
array_length(anyarray, int)
Description: Returns the length of the requested array dimension.
Return type: int
Example: Calculate the length of the array [1,2,3] in the first dimension.
1 2 3 4 5 | SELECT array_length(array[1,2,3], 1) AS RESULT; result -------- 3 (1 row) |
array_lower(anyarray, int)
Description: Returns lower bound of the requested array dimension.
Return type: int
Example: Query the lower bound of the array [0:2]={1,2,3} in the first dimension.
1 2 3 4 5 | SELECT array_lower('[0:2]={1,2,3}'::int[], 1) AS RESULT; result -------- 0 (1 row) |
array_upper(anyarray, int)
Description: Returns upper bound of the requested array dimension.
Return type: int
Example: Query the upper bound of the array [1, 8, 3, 7] in the first dimension.
1 2 3 4 5 | SELECT array_upper(ARRAY[1,8,3,7], 1) AS RESULT; result -------- 4 (1 row) |
array_to_string(anyarray, text [, text])
Description: Converts an array to a string, concatenates the elements using a specified delimiter, and defines how to handle NULL in the array.
The first parameter text is used as the new delimiter for the array, and the second parameter text is used to replace NULL in the array.
Return type: text
Example: Convert the array [1, 2, 3, NULL, 5] to a string, separate the elements with ,, and replace NULL with *.
1 2 3 4 5 | SELECT array_to_string(ARRAY[1, 2, 3, NULL, 5], ',', '*') AS RESULT; result ----------- 1,2,3,*,5 (1 row) |
The second parameter text of this function controls how NULL elements in the array are handled. If this parameter is omitted or explicitly set to NULL, the function ignores all NULL elements in the array and they will not appear in the final output string.
Example:
Omit the second parameter text.
1 2 3 4 5 | SELECT array_to_string(ARRAY[1, NULL, 3, NULL, 5], ',') AS result; result -------- 1,3,5 (1 row) |
Set the second parameter text to NULL.
1 2 3 4 5 | SELECT array_to_string(ARRAY[1, NULL, 3, NULL, 5], ',',NULL) AS RESULT; result -------- 1,3,5 (1 row) |
string_to_array(text, text [, text])
Description: Splits a string into an array based on a specified delimiter and replaces the specific value with NULL.
The second text indicates the specified delimiter. The third optional text replaces the specific value with NULL. If the separated substring exactly matches the third optional text, the substring is replaced with NULL.
Return type: text[]
Example: Split the string "xx~^~yy~^~zz" into arrays based on the delimiter "~^~" and replace the value "yy" with NULL.
1 2 3 4 5 | SELECT string_to_array('xx~^~yy~^~zz', '~^~', 'yy') AS RESULT; result -------------- {xx,NULL,zz} (1 row) |
The substring "yy" obtained after the string "xx~^~yy~^~zz" is split does not completely match the parameter "y" and will not be replaced with NULL.
1 2 3 4 5 | SELECT string_to_array('xx~^~yy~^~zz', '~^~', 'y') AS RESULT; result ------------ {xx,yy,zz} (1 row) |
- The third parameter text of the function controls whether to replace the specific value with NULL. If this parameter is omitted or explicitly set to NULL, the function replaces the empty substring between two consecutive delimiters in the original string with NULL.
Example:
Omit the third parameter text.
1 2 3 4 5
SELECT string_to_array('a,,b', ',') AS result; result ---------- {a,"",b} (1 row)
Set the third parameter text to NULL.
1 2 3 4 5
SELECT string_to_array('a,,b', ',', NULL) AS result; result ---------- {a,"",b} (1 row)
- In string_to_array, if the delimiter parameter is NULL, each character in the input string will become a separate element in the resulting array. If the delimiter is an empty string, the entire input string becomes a single-element array. Otherwise the input string is split at each occurrence of the delimiter string.
Example:
The delimiter parameter is NULL.
1 2 3 4 5
SELECT string_to_array('abc', NULL) AS result; result --------- {a,b,c} (1 row)
The delimiter is an empty string.
1 2 3 4 5
SELECT string_to_array('abcde', ' ')AS result; result --------- {abcde} (1 row)
array_flatten(anyarray)
Description: Converts a multidimensional array to a one-dimensional array. This function is supported only in clusters of version 9.1.1.100 or later.
Return type: anyarray
Example:
1 2 3 4 5 | SELECT array_flatten(ARRAY[1, 2], [3,4]]) AS RESULT; result ----------- 1,2,3,4 (1 row) |
array_distinct(anyarray)
Description: Returns the array after deduplication. NULL values are not counted. This function is supported only in clusters of version 9.1.1.100 or later.
Return type: anyarray
Example:
1 2 3 4 5 | SELECT array_distinct(ARRAY[1, 2, 3, 1, NULL, 5]) AS RESULT; result ----------- 1,2,3,5 (1 row) |
The array_distinct(), array_uniq(), array_sort(), and array_reversesort() functions support only one-dimensional arrays.
array_uniq(anyarray)
Description: Returns the number of elements in an array without duplicate values. If there is NULL in an array, NULL is also counted as an element. This function is supported only in clusters of version 9.1.1.100 or later.
Return type: int
Example:
1 2 3 4 5 | SELECT array_uniq(ARRAY[1, 2, 3, 1, NULL, 5]) AS RESULT; result --------- 5 (1 row) |
array_sort(anyarray)
Description: Returns an array sorted in ascending order. If there is NULL in an array, NULL is placed at the end of the output sorted array. This function is supported only in clusters of version 9.1.1.100 or later.
Return type: anyarray
Example:
1 2 3 4 5 | SELECT array_sort(ARRAY[1, 2, 3, 1, NULL, 5]) AS RESULT; result --------- 1,1,2,3,5,NULL (1 row) |
array_reversesort(anyarray)
Description: Returns an array sorted in descending order. If there is NULL in an array, NULL is placed at the end of the output sorted array. This function is supported only in clusters of version 9.1.1.100 or later.
Return type: anyarray
Example:
1 2 3 4 5 | SELECT array_reversesort(ARRAY[1, 2, 3, 1, NULL, 5]) AS RESULT; result --------- 5,3,2,1,1,NULL (1 row) |
unnest(anyarray)
Description: Expands an array to a set of rows.
Return type: setof anyelement
Example:
1 2 3 4 5 6 | SELECT unnest(ARRAY[1,2]) AS RESULT; result -------- 1 2 (2 rows) |
The unnest function is used together with the string_to_array(text, text [, text]) array. To convert an array to columns, the statement first splits a string into arrays by comma, and then converts the arrays into columns.
1 2 3 4 5 6 7 8 | SELECT unnest(string_to_array('a,b,c,d',',')) AS RESULT; result -------- a b c d (4 rows) |
interval(N, N1, N2, N3 ... )
Description: Searches for the last array index that is less than or equal to the target parameter n from the input integer array.
If n is NULL, -1 is returned. The interval() function does not support the interval(N, N1) scenario.
Return type: int
Example: Find the interval to which the number 10 belongs in a sequence of numbers and return the corresponding interval index.
1 2 3 4 5 | SELECT INTERVAL(10, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 10, 11) AS RESULT; result -------- 11 (1 row) |
split(text, text)
Description: Separates strings by a delimiter and returns an array.
The first parameter text is a string, and the second parameter text is a delimiter.
Return type: text[]
Example: Split the string "a-b-c-d-e" into multiple parts based on the delimiter "-" and return an array.
1 2 3 4 5 | SELECT SPLIT('a-b-c-d-e', '-') AS RESULT; result ------------- {a,b,c,d,e} (1 row) |
Split the string "a-b-c-d-e" based on the delimiter "-" and extract the fourth element.
1 2 3 4 5 | SELECT SPLIT('a-b-c-d-e', '-')[4] AS RESULT; result -------- d (1 row) |
What is your overall rating for this page?
Thank you very much for your feedback. We will continue working to improve the documentation.See the reply and handling status in My Cloud VOC.
For any further questions, feel free to contact us through the chatbot.
Chatbot
