如何在Athena中按字符串格式时间戳查询JSON数组数据？

我的数据以字符串形式存储为JSON数组，结构如下：

[{
    "data": [{
        "ct": "26.7"
    }, {
        "ct": "24.9"
    }, {
        "ct": "26.8"
    }],
    "timestamp": "1658102460"
}, {
    "data": [{
        "ct": "26.7"
    }, {
        "ct": "25.0"
    }],
    "timestamp": "1658102520"
}]

我需要在Athena中编写SELECT语句，筛选出时间戳介于X和Y之间的数据。之前尝试SELECT json_extract_scalar(insights, '$.timestamp') as ts FROM history返回空结果；用struct结合unnest交叉连接的方式查询时速度极慢，求高效解决方法。

高效解决方案 #

1. 核心问题解析 #

json_extract_scalar(insights, '$.timestamp')返回空的原因是：insights是JSON数组，而非单个JSON对象，$.timestamp路径仅适用于单个对象，必须先将数组展开为独立行才能提取时间戳。

2. 优化后的查询写法 #

方式一：用CTE分步解析（可读性高，适合复杂场景）

WITH parsed_arrays AS (
    SELECT json_parse(insights) AS json_data
    FROM history
),
unnested_rows AS (
    SELECT 
        element.timestamp::BIGINT AS ts,
        element.data AS ct_list
    FROM parsed_arrays,
         unnest(json_data) AS t(element)
)
SELECT *
FROM unnested_rows
WHERE ts BETWEEN <X> AND <Y>;

方式二：简化直接查询（代码更紧凑）

SELECT 
    element.timestamp::BIGINT AS ts,
    element.data AS ct_list
FROM history,
     unnest(json_parse(insights)) AS t(element)
WHERE element.timestamp::BIGINT BETWEEN <X> AND <Y>;

3. 性能优化要点 #

• 优先过滤分区：如果history是按时间分区的表，先通过分区字段（如dt）缩小数据范围，再解析JSON，避免全表扫描。例如：

  SELECT ...
  FROM history
  WHERE dt BETWEEN '2022-07-18' AND '2022-07-19' -- 先过滤分区
  , unnest(json_parse(insights)) AS t(element)
  WHERE element.timestamp::BIGINT BETWEEN <X> AND <Y>;
  

• 提前转换数据类型：将timestamp字符串转为BIGINT后再做范围筛选，比字符串比较性能更高，也能避免格式匹配问题。
• 避免重复解析：用CTE统一解析JSON数组，减少重复解析带来的资源消耗。

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