如何在AWS Athena(Trino)中提取嵌套JSON并转为结构化表？

首先，你遇到json_query(json_col,'lax $[*].addOns')返回null的核心原因是JSON路径与实际数据结构不匹配——如果json_col本身不是顶层数组（比如是包含数组的对象），$[*]这种针对数组的路径自然无法命中数据。下面是完整的解决方案，分步骤实现：

1. 先确认JSON结构（关键前提） #

先执行以下SQL查看样本数据的实际结构，明确addOns所在的层级：

SELECT id, json_col FROM your_table LIMIT 1;

举个常见的嵌套结构例子（你需要根据实际输出调整后续SQL）：

{
  "order_items": [
    {
      "main_item": "burger",
      "addOns": [
        {"cod": "fries", "price": 6.99},
        {"cod": "soda", "price": 3.99}
      ]
    }
  ]
}

2. 展开嵌套数组并提取字段 #

使用UNNEST依次展开顶层数组和addOns数组，同时提取cod和price字段：

WITH expanded_addons AS (
  SELECT
    id,
    -- 给每个addOns项按id分组编号，用于后续转宽表
    row_number() OVER (PARTITION BY id ORDER BY addon) AS item_seq,
    -- 提取cod作为名称（用scalar避免JSON格式）
    json_extract_scalar(addon, '$.cod') AS item_name,
    -- 提取price并转为数值类型（根据实际类型选double/decimal）
    CAST(json_extract_scalar(addon, '$.price') AS DOUBLE) AS item_price
  FROM your_table
  -- 第一步：展开顶层数组（这里假设顶层数组是order_items，根据实际结构调整路径）
  CROSS JOIN UNNEST(json_extract(json_col, '$.order_items')) AS t(main_item)
  -- 第二步：展开每个主项的addOns数组（LEFT JOIN保留无addOns的行）
  LEFT JOIN UNNEST(json_extract(main_item, '$.addOns')) AS t(addon) ON true
)

3. 转换为item1~item4的结构化宽表 #

用CASE WHEN结合聚合函数，将行转列生成目标结构：

SELECT
  id,
  MAX(CASE WHEN item_seq = 1 THEN item_name END) AS item1_name,
  MAX(CASE WHEN item_seq = 1 THEN item_price END) AS item1_price,
  MAX(CASE WHEN item_seq = 2 THEN item_name END) AS item2_name,
  MAX(CASE WHEN item_seq = 2 THEN item_price END) AS item2_price,
  MAX(CASE WHEN item_seq = 3 THEN item_name END) AS item3_name,
  MAX(CASE WHEN item_seq = 3 THEN item_price END) AS item3_price,
  MAX(CASE WHEN item_seq = 4 THEN item_name END) AS item4_name,
  MAX(CASE WHEN item_seq = 4 THEN item_price END) AS item4_price
FROM expanded_addons
GROUP BY id;

关键注意事项 #

• 如果json_col本身就是顶层数组（而非包含数组的对象），则将UNNEST(json_extract(json_col, '$.order_items'))替换为UNNEST(json_col)即可。
• 若addOns数量超过4个，可继续扩展CASE WHEN分支；若不足4个，对应字段会返回null。
• lax模式下路径不匹配不会报错，但会返回null，调试时可换成strict模式（strict $[*].addOns），它会直接抛出错误，帮助定位路径问题。

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