ARRAY_ENUMERATE

array_enumerate

SinceVersion 2.0.0

Description

Returns the position index (starting from 1) for each element in the array. The function generates corresponding position numbers for each element in the array.

Syntax

array_enumerate(ARRAY<T> arr)

Parameters

• arr：ARRAY type, the array for which to generate position indices. Supports column names or constant values.

Supported types for T:

• Numeric types: TINYINT, SMALLINT, INT, BIGINT, LARGEINT, FLOAT, DOUBLE, DECIMAL
• String types: CHAR, VARCHAR, STRING
• Date and time types: DATE, DATETIME, DATEV2, DATETIMEV2
• Boolean type: BOOLEAN
• IP types: IPV4, IPV6
• Complex types: ARRAY, MAP, STRUCT

Return Value

Return type: ARRAY

Return value meaning:

• Returns a new array with the same length as the input array, where each position contains the position index (starting from 1) of the corresponding element in the array
• NULL: if the input array is NULL

Usage notes:

• The function generates position indices for each element in the array, starting from 1 and incrementing
• Empty arrays return empty arrays, NULL arrays return NULL
• For null values in array elements: null elements also generate corresponding position indices

Examples

Query Examples:

Generate position indices for an array:

SELECT array_enumerate([1, 2, 1, 4, 5]);
+----------------------------------+
| array_enumerate([1, 2, 1, 4, 5]) |
+----------------------------------+
| [1, 2, 3, 4, 5]                  |
+----------------------------------+

Empty array returns empty array:

SELECT array_enumerate([]);
+----------------------+
| array_enumerate([])  |
+----------------------+
| []                   |
+----------------------+

Array containing null values, null elements also generate position indices:

SELECT array_enumerate([1, null, 3, null, 5]);
+--------------------------------------------+
| array_enumerate([1, null, 3, null, 5])     |
+--------------------------------------------+
| [1, 2, 3, 4, 5]                            |
+--------------------------------------------+

Complex type examples:

Nested array types:

SELECT array_enumerate([[1,2],[3,4],[5,6]]);
+----------------------------------------+
| array_enumerate([[1,2],[3,4],[5,6]])   |
+----------------------------------------+
| [1, 2, 3]                              |
+----------------------------------------+

Map types:

SELECT array_enumerate([{'k':1},{'k':2},{'k':3}]);
+----------------------------------------------+
| array_enumerate([{'k':1},{'k':2},{'k':3}])   |
+----------------------------------------------+
| [1, 2, 3]                                    |
+----------------------------------------------+

Struct types:

SELECT array_enumerate(array(named_struct('name','Alice','age',20),named_struct('name','Bob','age',30)));
+----------------------------------------------------------------------------------------+
| array_enumerate(array(named_struct('name','Alice','age',20),named_struct('name','Bob','age',30))) |
+----------------------------------------------------------------------------------------+
| [1, 2]                                                                                  |
+----------------------------------------------------------------------------------------+

Error when parameter count is wrong:

SELECT array_enumerate([1,2,3], [4,5,6]);
ERROR 1105 (HY000): errCode = 2, detailMessage = Can not found function 'array_enumerate' which has 2 arity. Candidate functions are: [array_enumerate(Expression)]

Error when passing non-array type:

SELECT array_enumerate('not_an_array');
ERROR 1105 (HY000): errCode = 2, detailMessage = Can not find the compatibility function signature: array_enumerate(VARCHAR(12))

Keywords

ARRAY, ENUMERATE, ARRAY_ENUMERATE