如何在AWS Athena解析无效JSON时忽略错误，仅返回成功记录

我们使用AWS Athena存储订单和产品信息，orders表的line-items列存储的是非标准JSON字符串。原本的处理逻辑是先将其转换为有效JSON再解析，把line-items中的不同产品拆分为单独行展示，但部分记录的JSON解析会失败，直接导致整个结果集抛出错误。需要调整方案，忽略解析失败的记录（对应字段返回null），同时保留能成功解析的行。

原报错查询如下：

with orders_info AS (
        select id as order_id, created_at, substring(created_at, 1, 10) as order_date, 
    customer_id, customer_email, customer_phone, billing_address_country, line_items, total_price_set_shop_money_amount,
    row_number() over(partition by id order by etl_run_date asc) AS rn
    from orders where cast(substring(created_at, 1, 10) as date) = CURRENT_DATE - INTERVAL '2' DAY
    and id in ('134', '4545')
    ),
    
    orders_dataset as (
        select *, replace(replace(replace(replace(line_items, 'None', '''none'''), 'True', 'true'), 'False', 'false'), '''', '"') as line_items_json
            from orders_info where rn = 1
    ),
    
    line_items_dataset as (
    select od.*, json_extract_scalar(m, '$.id') product_id,
           json_extract_scalar(m, '$.variant_id') variant_id,
           json_extract_scalar(m, '$.price_set.shop_money.amount') price_set_shop_money_amount,
           json_extract_scalar(m, '$.price_set.shop_money.currency_code') price_set_shop_money_currency_code
    from orders_dataset od,
        unnest(cast(json_parse(line_items_json) as array(json))) as t(m) 
    )
    
    select * from line_items_dataset

调整后的查询方案 #

核心是用try()函数包裹json_parse，让解析失败时返回null而非抛出错误；同时用coalesce配合空数组处理解析失败后的情况，避免unnest出错：

with orders_info AS (
        select id as order_id, created_at, substring(created_at, 1, 10) as order_date, 
    customer_id, customer_email, customer_phone, billing_address_country, line_items, total_price_set_shop_money_amount,
    row_number() over(partition by id order by etl_run_date asc) AS rn
    from orders where cast(substring(created_at, 1, 10) as date) = CURRENT_DATE - INTERVAL '2' DAY
    and id in ('134', '4545')
    ),
    
    orders_dataset as (
        select *, replace(replace(replace(replace(line_items, 'None', '''none'''), 'True', 'true'), 'False', 'false'), '''', '"') as line_items_json
            from orders_info where rn = 1
    ),
    
    line_items_dataset as (
    select od.*, 
           json_extract_scalar(m, '$.id') product_id,
           json_extract_scalar(m, '$.variant_id') variant_id,
           json_extract_scalar(m, '$.price_set.shop_money.amount') price_set_shop_money_amount,
           json_extract_scalar(m, '$.price_set.shop_money.currency_code') price_set_shop_money_currency_code
    from orders_dataset od,
        -- 用try()包裹json_parse，解析失败返回null；coalesce将null转为空数组，避免unnest报错
        unnest(coalesce(cast(try(json_parse(line_items_json)) as array(json)), array[])) as t(m) 
    )
    
    select * from line_items_dataset

关键说明 #

• try(json_parse(line_items_json))：当JSON解析失败时，不会抛出异常，而是返回null，确保单个无效记录不会中断整个查询。
• coalesce(..., array[])：将解析失败产生的null转为空数组，unnest处理空数组时不会生成任何行，自然跳过这条无效的订单记录；如果解析成功，则正常展开数组为多行产品记录。

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