Redshift中无法将字符串转为数字问题求助

错误原因 #

报错cannot cast type cstring to numeric的核心原因是：json_extract_array_element_text(offer.disbursement_requirements, 2, true)返回的字符串无法被强制转换为numeric类型。常见触发场景包括：

• 该JSON数组元素是空字符串""
• 元素内容包含非数字字符（如字母、特殊符号）
• 元素格式不符合numeric规范（如多个小数点）

另外你的SQL存在冗余CAST写法（如cast(json_extract_array_element_text(...) :: numeric)），虽不直接引发错误，但会增加代码复杂度。

修复方案 #

方案1：用Safe_cast避免转换报错 #

Redshift的safe_cast函数在转换失败时返回null而非抛出错误，适配JSON中可能存在的非数字内容：

case 
    when offer.disbursement_required = 'true' 
    and (
        safe_cast(json_extract_array_element_text(offer.disbursement_requirements, 2, true) as numeric) <> offer.amount 
        or (safe_cast(json_extract_array_element_text(offer.disbursement_requirements, 2, true) as numeric) is null or offer.amount is null)
    )
    and not (safe_cast(json_extract_array_element_text(offer.disbursement_requirements, 2, true) as numeric) is null and offer.amount is null) 
then 1
end

方案2：先校验字符串格式再转换 #

若需先过滤无效数字字符串，可结合regexp_like判断合法数字格式：

case 
    when offer.disbursement_required = 'true' 
    and (
        case 
            when regexp_like(json_extract_array_element_text(offer.disbursement_requirements, 2, true), '^-?\d+(\.\d+)?$') 
            then json_extract_array_element_text(offer.disbursement_requirements, 2, true)::numeric 
            else null 
        end <> offer.amount 
        or (
            case 
                when regexp_like(json_extract_array_element_text(offer.disbursement_requirements, 2, true), '^-?\d+(\.\d+)?$') 
                then json_extract_array_element_text(offer.disbursement_requirements, 2, true)::numeric 
                else null 
            end is null 
            or offer.amount is null
        )
    )
    and not (
        case 
            when regexp_like(json_extract_array_element_text(offer.disbursement_requirements, 2, true), '^-?\d+(\.\d+)?$') 
            then json_extract_array_element_text(offer.disbursement_requirements, 2, true)::numeric 
            else null 
        end is null 
        and offer.amount is null
    ) 
then 1
end

优化建议 #

将重复的JSON提取转换逻辑用CTE封装，提升代码可读性：

with offer_processed as (
    select 
        *,
        safe_cast(json_extract_array_element_text(disbursement_requirements, 2, true) as numeric) as disbursement_req_numeric
    from offer
)
select 
    case 
        when disbursement_required = 'true' 
        and (
            disbursement_req_numeric <> amount 
            or (disbursement_req_numeric is null or amount is null)
        )
        and not (disbursement_req_numeric is null and amount is null) 
    then 1
    end as result
from offer_processed

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