Help Center/Log Tank Service/User Guide/Log Search and Analysis (Pipe Character)/SQL Functions/Array Functions
Updated on 2025-08-14 GMT+08:00
View PDF

Array Functions

This section describes array functions, including their syntax, parameters, and usage examples.

Function List

Table 1 Array functions

Function

Description

array

Constructs the input parameters of the same type into an array.

array_position

Obtains the subscript of a specified element, starting from 1. If the specified element was not found, 0 is returned.

cardinality

Calculates the number of elements in an array.

mv_length

Calculates the number of elements in an array. It is equivalent to the cardinality function.

contains

Determines whether an array contains a specified element. If the array contains the element, true is returned.

mv_contains

Determines whether an array contains a specified element. If the array contains the element, true is returned. This function is equivalent to the contains function.

mv_prepend

Adds a specified element to the beginning of an array.

mv_append

Adds a specified element to the end of an array.

mv_slice

Returns a portion of an array from index start to index end.

mv_to_string

Uses a specified delimiter (str) to join all elements of an array (arr) into a string.

string_to_mv

Uses a specified delimiter (str2) to split a string (str1) into an array.

mv_offset

Returns the array element at the provided 0-based index.

mv_ordinal

Returns the array element at the provided 1-based index.

mv_offset_of

Returns the 0-based index of expr that appears for the first time in an array. If expr does not appear, it returns -1.

mv_ordinal_of

Returns the 1-based index of expr that appears for the first time in an array. If expr does not appear, it returns -1.

array

Constructs the parameters of the same type into an array.

Syntax: array[expr1,expr...]

Table 2 Parameter description

Parameter

Description

Type

Mandatory

expr

Raw data.

String/Integer/Long/Double/Float

Yes

Return value type: array

Example: SELECT ARRAY['1','2','3','4','5']

Table 3 Query and analysis results

Type

Query Statement

Returned Result

Scenario

ARRAY['1','2','3','4','5']

["1", "2", "3", "4", "5"]

array_position

Obtains the subscript of a specified element, starting from 1. If the specified element was not found, 0 is returned.

Syntax: array_position(expr, ele)

Table 4 Parameter description

Parameter

Description

Type

Mandatory

expr

Original array.

Array

Yes

ele

Specified element.

An element in the array. It must be of the same type as the other elements in the array.

Yes

Return value type: integer

Example: SELECT ARRAY_POSITION(ARRAY['1','2','3'],'2')

Table 5 Query and analysis results

Type

Query Statement

Returned Result

Scenario

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

2

cardinality

This function calculates the number of elements in an array. The elements must be of the same parameter type.

Syntax: cardinality(expr)

Table 6 Parameter description

Parameter

Description

Type

Mandatory

expr

Original array.

Array (string/number)

Yes

Return value type: integer

Example: SELECT CARDINALITY(ARRAY['1','2','3'])

Table 7 Query and analysis results

Type

Query Statement

Returned Result

Scenario

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

3

mv_length

This function calculates the number of elements in an array. It is equivalent to the cardinality function.

Syntax: mv_length(expr)

Table 8 Parameter description

Parameter

Description

Type

Mandatory

expr

Original array.

Array (string/number)

Yes

Return value type: integer

Example: SELECT MV_LENGTH(ARRAY['1','2','3'])

Table 9 Query and analysis results

Type

Query Statement

Returned Result

Scenario

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

3

contains

This function determines whether an array contains a specified element. If the array contains the element, true is returned.

Syntax: contains(expr, ele)

Table 10 Parameter description

Parameter

Description

Type

Mandatory

expr

Original array.

Array (string/number)

Yes

ele

Specified element.

String/Number. The type must be the same as that of the element in the array.

Yes

Return value type: Boolean

Example: SELECT CONTAINS(ARRAY['1','2'],'1')

Table 11 Query and analysis results

Type

Query Statement

Returned Result

Scenario

CONTAINS(ARRAY['1','2'],'1')

true

mv_contains

This function determines whether an array contains a specified element. If the array contains the element, true is returned. This function is equivalent to the contains function.

Syntax: mv_contains(expr, ele)

Table 12 Parameter description

Parameter

Description

Type

Mandatory

expr

Original array.

Array (string/number)

Yes

ele

Specified element.

String/Number. The type must be the same as that of the element in the array.

Yes

Return value type: Boolean

Example: SELECT MV_CONTAINS(ARRAY['1','2'],'1')

Table 13 Query and analysis results

Type

Query Statement

Returned Result

Scenario

MV_CONTAINS(ARRAY['1','2'],'1')

true

mv_prepend

This function adds a specified element to the beginning of an array.

Syntax: mv_prepend(expr, arr)

Table 14 Parameter description

Parameter

Description

Type

Mandatory

expr

Specified element.

String/Number. The type must be the same as that of the element in the array.

Yes

arr

Original array.

Array (string/number)

Yes

Return value type: array

Example: SELECT MV_PREPEND('1', ARRAY ['1','2'])

Table 15 Query and analysis results

Type

Query Statement

Returned Result

Scenario

MV_PREPEND ('1', ARRAY['1','2'])

["1","1","2"]

mv_append

This function adds a specified element to the end of an array.

Syntax: mv_append(arr, expr)

Table 16 Parameter description

Parameter

Description

Type

Mandatory

arr

Original array.

Array (string/number)

Yes

expr

Specified element.

String/Number. The type must be the same as that of the element in the array.

Yes

Return value type: array

Example: SELECT MV_APPEND(ARRAY['1','2'],'1')

Table 17 Query and analysis results

Type

Query Statement

Returned Result

Scenario

MV_APPEND(ARRAY['1','2'], '1')

["1","2","1"]

mv_slice

This function returns a portion of an array from index start to index end.

Syntax: mv_slice(arr, start, end)

Table 18 Parameter description

Parameter

Description

Type

Mandatory

arr

Original array.

Array (string/number)

Yes

start

Start position.

Integer

Yes

end

End position.

Integer

Yes

Return value type: array

Example: SELECT MV_SLICE(ARRAY['1','2','3','4','5'], 2, 4)

Table 19 Query and analysis results

Type

Query Statement

Returned Result

Scenario

MV_SLICE(ARRAY['1','2','3','4','5'], 2, 4)

["3","4"]

mv_to_string

This function uses a specified delimiter (str) to join all elements of an array (arr) into a string.

Syntax: mv_to_string(arr, str)

Table 20 Parameter description

Parameter

Description

Type

Mandatory

arr

Original array.

Array (string/number)

Yes

str

Specified delimiter.

String

Yes

Return value type: string

Example: SELECT MV_TO_STRING(ARRAY['1','2','3','4','5'],'-')

Table 21 Query and analysis results

Type

Query Statement

Returned Result

Scenario

MV_TO_STRING(ARRAY['1','2','3','4','5'],'-')

1-2-3-4-5

string_to_mv

This function uses a specified delimiter (str2) to split a string (str1) into an array.

Syntax: string_to_mv(str1, str2)

Table 22 Parameter description

Parameter

Description

Type

Mandatory

str1

Original string.

String

Yes

str2

Specified delimiter.

String

Yes

Return value type: array

Example: SELECT STRING_TO_MV('1-2-3-4-5','-')

Table 23 Query and analysis results

Type

Query Statement

Returned Result

Scenario

STRING_TO_MV('1-2-3-4-5','-')

["1","2","3","4","5"]

mv_offset

This function returns the array element at the provided 0-based index. If the index is out of bounds, it returns null.

Syntax: mv_offset(arr, index)

Table 24 Parameter description

Parameter

Description

Type

Mandatory

arr

Original array.

Array (string/number)

Yes

index

Specified index position.

Integer

Yes

Return value type: string/integer/long/Boolean/double

Example: SELECT MV_OFFSET(ARRAY['1','2','3','4','5'], 2)

Table 25 Query and analysis results

Type

Query Statement

Returned Result

Scenario

MV_OFFSET(ARRAY['1','2','3','4','5'], 2)

3

mv_ordinal

This function returns the array element at the provided 1-based index. If the index is out of bounds, it returns null.

Syntax: mv_ordinal(arr, index)

Table 26 Parameter description

Parameter

Description

Type

Mandatory

arr

Original array.

Array (string/number)

Yes

index

Specified index position.

Integer

Yes

Return type: string/integer/long/Boolean/double

Example: SELECT MV_ORDINAL(ARRAY['1','2','3','4','5'], 2)

Table 27 Query and analysis results

Type

Query Statement

Returned Result

Scenario

MV_ORDINAL (ARRAY['1','2','3','4','5'], 2)

2

mv_offset_of

This function returns the 0-based index of expr that appears for the first time in an array. If expr does not appear, it returns -1.

Syntax: mv_offset_of(arr, expr)

Table 28 Parameter description

Parameter

Description

Type

Mandatory

arr

Original array.

Array (string/number)

Yes

expr

Specified element.

String/Number. The type must be the same as that of the element in the array.

Yes

Return value type: integer

Example: SELECT MV_OFFSET_OF(ARRAY['1','2','3','4','5'], '2')

Table 29 Query and analysis results

Type

Query Statement

Returned Result

Scenario

MV_OFFSET_OF(ARRAY['1','2','3','4','5'], '2')

1

mv_ordinal_of

This function returns the 1-based index of expr that appears for the first time in an array. If expr does not appear, it returns -1.

Syntax: mv_ordinal_of(arr, expr)

Table 30 Parameter description

Parameter

Description

Type

Mandatory

arr

Original array.

Array (string/number)

Yes

expr

Specified element.

String/Number. The type must be the same as that of the element in the array.

Yes

Return value type: integer

Example: SELECT MV_ORDINAL_OF(ARRAY['1','2','3','4','5'], '2')

Table 31 Query and analysis results

Type

Query Statement

Returned Result

Scenario

MV_ORDINAL_OF(ARRAY['1','2','3','4','5'], '2')

2

Parent topic:SQL Functions