如何在Athena中提取JSON字段里FAILED=TRUE的记录？

我在Athena表中存储了如下JSON数据：

{
    "VALIDATION_TYPE": "ROW_BY_ROW",
    "DATABASE": "erp",
    "TABLES": {
        "APPLICATION_STATUS_TYPE": {
            "BATCH_VALIDATION": {
                "BATCHES": [{
                    "0": {
                        "FAILED": "FALSE",
                        "FAILURE_MSG": ""
                    }
                }, {
                    "1": {
                        "FAILED": "TRUE",
                        "FAILURE_MSG": "NULL POINTER EXCEPTION"
                    }

                }]
            }
        },
        "APPLICATION": {
            "BATCH_VALIDATION": {
                "BATCHES": [{
                    "0": {
                        "FAILED": "FALSE",
                        "FAILURE_MSG": ""
                    }
                }, {
                    "1": {
                        "FAILED": "TRUE",
                        "FAILURE_MSG": "NULL POINTER EXCEPTION"
                    }
                }]
            }
        }
    }
}

需要编写Athena/Presto查询语句，提取所有FAILED=TRUE的记录，预期输出格式如下：

VALIDATION_TYPE,DATABASE,TABLE,ID,FAILED,FAILURE_MSG
----------------------------------------------------
ROW_BY_ROW,erp,APPLICATION_STATUS_TYPE,1,TRUE,NULL POINTER EXCEPTION
ROW_BY_ROW,erp,APPLICATION,1,TRUE,NULL POINTER EXCEPTION

我已尝试使用TRANSFORM、UNNEST、JSON_EXTRACT等函数，但未成功实现需求，恳请指导适用的特定函数或解决方案。

可以通过多层扁平化嵌套结构结合map_entries()、UNNEST、json_extract_scalar()函数实现需求，具体查询语句如下：

WITH parsed_data AS (
    SELECT
        json_extract_scalar(json_column, '$.VALIDATION_TYPE') AS VALIDATION_TYPE,
        json_extract_scalar(json_column, '$.DATABASE') AS DATABASE,
        -- 将TABLES对象转为键值对数组，拆分表名与表内容
        map_entries(json_extract(json_column, '$.TABLES')) AS table_entries
    FROM your_table_name -- 替换为你的实际表名
),
table_level AS (
    SELECT
        VALIDATION_TYPE,
        DATABASE,
        entry.key AS TABLE_NAME,
        -- 提取每个表对应的BATCHES数组
        json_extract(entry.value, '$.BATCH_VALIDATION.BATCHES') AS batches_array
    FROM parsed_data
    CROSS JOIN UNNEST(table_entries) AS t(entry)
),
batch_level AS (
    SELECT
        VALIDATION_TYPE,
        DATABASE,
        TABLE_NAME,
        -- 展开BATCHES数组中的每个元素
        batch_element
    FROM table_level
    CROSS JOIN UNNEST(batches_array) AS t(batch_element)
),
id_level AS (
    SELECT
        VALIDATION_TYPE,
        DATABASE,
        TABLE_NAME,
        -- 将单个batch对象转为键值对数组，拆分ID与校验详情
        map_entries(batch_element) AS id_entries
    FROM batch_level
),
final_details AS (
    SELECT
        VALIDATION_TYPE,
        DATABASE,
        TABLE_NAME,
        id_entry.key AS ID,
        json_extract_scalar(id_entry.value, '$.FAILED') AS FAILED,
        json_extract_scalar(id_entry.value, '$.FAILURE_MSG') AS FAILURE_MSG
    FROM id_level
    CROSS JOIN UNNEST(id_entries) AS t(id_entry)
)
SELECT
    VALIDATION_TYPE,
    DATABASE,
    TABLE_NAME AS TABLE,
    ID,
    FAILED,
    FAILURE_MSG
FROM final_details
WHERE FAILED = 'TRUE';

关键函数说明 #

• map_entries()：将嵌套的JSON对象转换为键值对数组，解决TABLES和单个batch对象的层级拆分问题
• UNNEST：依次展开表条目数组、BATCHES数组、ID条目数组，把多层嵌套结构转为扁平的行数据
• json_extract_scalar()：提取JSON字段中的字符串值，确保输出字段类型符合预期

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