AWS Redshift中array_length(text[])报错问题及解决咨询

问题复现 #

执行以下Redshift查询语句时出现数组操作报错：

SELECT
    user_uuid,
    genre,
    COUNT(*) AS occurrences,
    LISTAGG(content_id, ',') WITHIN GROUP (ORDER BY content_id) AS content_ids_array,
    ARRAY_LENGTH(STRING_TO_ARRAY(LISTAGG(content_id, ',') WITHIN GROUP (ORDER BY content_id), ','))::INT AS content_ids_array_length
FROM
    info_master
WHERE
    event_date BETWEEN '2023-09-01' AND '2023-09-30'
GROUP BY
    user_uuid,
    genre;

报错信息：

ERROR: function array_length(text[]) does not exist Hint: No function matches the given name and argument types. You may need to add explicit type casts.

已知content_id的数据类型为character varying(256)。

报错原因 #

Redshift中的array_length函数仅支持super类型的数组，而STRING_TO_ARRAY返回的是text[]类型数组，两者类型不匹配，因此触发报错。

解决方法 #

方法1：用REGEXP_COUNT统计元素个数（推荐） #

既然content_ids_array是逗号分隔的字符串，直接统计逗号数量再加1就能得到元素总数，无需转换数组：

SELECT
    user_uuid,
    genre,
    COUNT(*) AS occurrences,
    LISTAGG(content_id, ',') WITHIN GROUP (ORDER BY content_id) AS content_ids_array,
    -- 处理空值情况：如果content_ids_array为空，返回0，否则统计逗号数+1
    CASE 
        WHEN content_ids_array IS NULL THEN 0
        ELSE REGEXP_COUNT(content_ids_array, ',') + 1 
    END AS content_ids_array_length
FROM
    info_master
WHERE
    event_date BETWEEN '2023-09-01' AND '2023-09-30'
GROUP BY
    user_uuid,
    genre;

方法2：转换为super类型数组后使用array_length #

把STRING_TO_ARRAY的结果显式转换为super类型，再调用array_length：

SELECT
    user_uuid,
    genre,
    COUNT(*) AS occurrences,
    LISTAGG(content_id, ',') WITHIN GROUP (ORDER BY content_id) AS content_ids_array,
    array_length(STRING_TO_ARRAY(LISTAGG(content_id, ',') WITHIN GROUP (ORDER BY content_id), ',')::super) AS content_ids_array_length
FROM
    info_master
WHERE
    event_date BETWEEN '2023-09-01' AND '2023-09-30'
GROUP BY
    user_uuid,
    genre;

Redshift数组函数支持情况 #

• 针对传统SQL数组（如text[]、int[]）的函数有限，仅支持array_join、array_position、array_append等基础操作，不支持array_length、array_slice等函数。
• 针对super类型数组的函数较为完善，包括array_length、array_slice、array_contains、array_sort等，且支持嵌套数组操作。如果需要复杂数组处理，建议使用super类型。

内容的提问来源于stack exchange，提问作者David Faizulaev