Athena中UNNEST JSON报错求助：无法展开varchar/json类型

报错原因 #

你遇到的两个错误本质是因为UNNEST只能处理数组类型的数据，而原写法存在逻辑问题：

• 直接用json_query返回的是字符串类型（即使套了json_array也还是字符串），所以第一个报错提示无法UNNEST varchar类型；
• 转为JSON后，json_query返回的是包含多个数组的JSON对象（比如matchId=1的情况是两个matchDate数组嵌套在一个JSON里），并非可直接UNNEST的一维数组，因此第二个报错提示无法UNNEST json类型。

正确SQL写法 #

要实现目标输出，需要分步骤展开嵌套的JSON数组，同时处理缺失字段的NULL情况：

WITH matches AS (
    SELECT 1 AS matchId, '
    {
      "matchDetail": [
        {
          "matchType": "Practice",
          "matchDate": [
            {
              "rainyDate": "2024-09-01",
              "sunnyDate": "2024-09-02"
            },
            {
              "rainyDate": "2024-09-07",
              "sunnyDate": "2024-09-07"
            }
          ]
        },
        {
          "matchType": "Match",
          "matchDate": [
            {
              "rainyDate": "2024-09-04",
              "sunnyDate": "2024-09-04"
            },
            {
              "rainyDate": "2024-09-11",
              "sunnyDate": "2024-09-12"
            },
            {
              "rainyDate": "2024-09-18",
              "sunnyDate": "2024-09-19"
            }
          ]
        }
      ]
    }' AS matchDetails
    UNION
    SELECT 2, '
    {
      "matchDetail": [
        {
          "matchType": "Match",
          "matchDate": [
            {
              "rainyDate": "2024-10-04",
              "sunnyDate": "2024-10-04"
            },
            {
              "rainyDate": "2024-10-11"
            }
          ]
        }
      ]
    }'
    UNION
    SELECT 3, '
    {
      "matchDetail": [
        {
          "matchType": "Match"
        }
      ]
    }'
)
SELECT
    m.matchId,
    COALESCE(d.value:rainyDate::VARCHAR, '') AS rainyDate,
    COALESCE(d.value:sunnyDate::VARCHAR, '') AS sunnyDate
FROM matches m
-- 第一步：展开matchDetail数组
CROSS JOIN LATERAL FLATTEN(INPUT => PARSE_JSON(m.matchDetails):matchDetail) AS md
-- 过滤只保留Match类型的赛事
WHERE md.value:matchType::VARCHAR = 'Match'
-- 第二步：展开matchDate数组，如果matchDate不存在则生成一条空记录
LEFT JOIN LATERAL FLATTEN(INPUT => COALESCE(md.value:matchDate, PARSE_JSON('[]'))) AS d
ORDER BY m.matchId;

关键步骤说明 #

1. PARSE_JSON转换：先把字符串类型的matchDetails转为JSON对象，才能进行数组展开操作；
2. FLATTEN展开外层数组：用FLATTEN展开matchDetail数组，得到每个赛事详情元素；
3. 过滤Match类型：通过WHERE子句筛选出非Practice的赛事；
4. 处理内层matchDate数组：用LEFT JOIN LATERAL FLATTEN展开matchDate数组，同时用COALESCE处理matchDate缺失的情况（比如matchId=3），确保即使没有日期数组也能生成一条空记录；
5. 提取字段并处理NULL：用COALESCE把NULL转为空字符串，和目标输出格式一致。

内容的提问来源于stack exchange，提问作者Dhruva Sen Gupta