AWS Athena如何过滤数组中含最新日期的字典？

先把你提供的JSON结构修正为合法格式：

{
  "AA": {
    "BB": {
      "CC": [
        {
          "lastUpdateDate": "2020-10-18"
        },
        {
          "lastUpdateDate": "2021-10-18"
        }
      ]
    }
  }
}

你可以结合UNNEST和窗口函数来实现需求，具体SQL示例如下（假设你的表名为your_table，存储JSON的字段名为data）：

WITH unnested_data AS (
  SELECT
    -- 若表中有主键（比如id），请在此添加主键字段，避免多条记录混淆
    json_extract_scalar(cc_element, '$.lastUpdateDate') AS last_update_date,
    cc_element AS full_element,
    -- 计算当前数据集内的最大更新日期，若按单条主记录分组，需加 PARTITION BY 主键
    MAX(json_extract_scalar(cc_element, '$.lastUpdateDate')) OVER () AS max_update_date
  FROM your_table
  -- 展开AA.BB.CC数组
  CROSS JOIN UNNEST(json_extract(data, '$.AA.BB.CC')) AS t(cc_element)
)
-- 过滤出更新日期等于最大日期的元素
SELECT full_element
FROM unnested_data
WHERE last_update_date = max_update_date;

关键说明： #

• CROSS JOIN UNNEST会把数组AA.BB.CC里的每个元素拆成单独的数据行，方便后续处理
• MAX() OVER ()窗口函数用来获取所有展开行中的最新日期，如果你的表有多条独立的JSON记录，记得在OVER()里加上PARTITION BY 主键字段，确保每个主记录只取自己数组里的最新元素
• 最终通过过滤条件筛选出对应最新日期的完整字典元素

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