AWS Athena JSON深度扫描：JSONPath..操作符查询嵌套字段失败问询

AWS Athena 原生 JSON 函数不支持 JSONPath 中的递归下降操作符 ..，要实现任意嵌套层级的键查询，可以使用以下替代方案：

方案1：使用 json_tree 函数递归遍历所有JSON节点 #

该方案完全等效于递归匹配任意嵌套层级的目标键，无需提前知道JSON结构：

WITH dataset AS (
   SELECT '{"name": "aa",
             "id":-100,
             "org": "engineering",
             "key":{"val":1000},
             "projects": [{"name":"project1", "completed":false},{"name":"project2", "completed":true}]}'
     AS blob1
)
SELECT * FROM dataset
WHERE EXISTS (
    SELECT 1 
    FROM UNNEST(json_tree(blob1)) AS t(node)
    WHERE 
        json_extract_scalar(node, '$.key') = 'val' -- 匹配目标键名
        AND json_extract_scalar(node, '$.value') = '1000' -- 匹配目标值
)

json_tree 会把输入JSON的所有嵌套节点展开为数组，每个节点包含key（键名）、value（键值）、path（节点全路径）三个属性，可以按需过滤。

方案2：已知嵌套层级范围时拼接多路径匹配 #

如果你的JSON结构嵌套深度可控，提前梳理出所有可能的路径后用OR拼接即可，查询性能比全量遍历JSON更高：

WITH dataset AS (
   SELECT '{"name": "aa",
             "id":-100,
             "org": "engineering",
             "key":{"val":1000},
             "projects": [{"name":"project1", "completed":false},{"name":"project2", "completed":true}]}'
     AS blob1
)
SELECT * FROM dataset 
WHERE 
    json_extract_scalar(blob1, '$.val') = '1000'
    OR json_extract_scalar(blob1, '$.key.val') = '1000'
    OR json_extract_scalar(blob1, '$.projects[*].val') = '1000'
    -- 按需补充其他可能的路径

方案3：建表时提前解析嵌套结构 #

如果是固定场景的高频查询，可以在创建Athena表时直接定义嵌套的struct/array结构，省去每次查询时的JSON解析开销：

CREATE EXTERNAL TABLE your_json_table (
    name string,
    id int,
    org string,
    key struct<val:int>,
    projects array<struct<name:string, completed:boolean>>
)
ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe'
LOCATION 's3://你的存储桶路径/'

后续查询可以直接用SELECT key.val FROM your_json_table这类语法，无需调用JSON解析函数。

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