Athena SQL：如何将UNNEST后的JSON键值对合并到同行列

我在Athena中有如下JSON格式数据：

[{"name": "agreementUrl", "value": "agmt-id00001"}, {"name": "sellerOfRecord", "value": "ABC Corporation"}]
[{"name": "agreementUrl", "value": "agmt-id00002"}, {"name": "sellerOfRecord", "value": "XYZ Corporation"}]

注：原始输入的JSON为简化格式，实际Athena中需使用标准JSON（键名加引号）。

需要将agreementUrl对应的Agreement ID和sellerOfRecord的值分别放在独立列中。当前使用以下查询取出数据，但结果为分行展示：

SELECT
  license_metadata.name, license_metadata.value
FROM
  json_licensedata
CROSS JOIN UNNEST(licensemetadata) t (license_metadata)

当前查询结果 #

期望结果 #

解决方案 #

可以通过条件聚合或PIVOT语法将同一组的键值对合并到同一行，核心是确保同一JSON数组内的字段能被正确分组。

方法一：条件聚合 #

利用MAX(CASE WHEN ...)提取对应字段值，由于每组数据中每个name仅出现一次，MAX函数可确保获取唯一值：

SELECT
  MAX(CASE WHEN license_metadata.name = 'agreementUrl' THEN license_metadata.value END) AS "Agreement ID",
  MAX(CASE WHEN license_metadata.name = 'sellerOfRecord' THEN license_metadata.value END) AS sellerOfRecord
FROM
  json_licensedata
CROSS JOIN UNNEST(licensemetadata) t (license_metadata)
-- 按原始表的分组键聚合，若每一行对应一个JSON数组，直接GROUP BY整行；若有唯一主键（如id），替换为GROUP BY id
GROUP BY json_licensedata

方法二：PIVOT语法 #

Athena支持PIVOT操作，可直观将行转换为列：

SELECT
  "agreementUrl" AS "Agreement ID",
  "sellerOfRecord"
FROM
  (
    SELECT
      license_metadata.name,
      license_metadata.value,
      -- 用原始表的唯一标识分组，若没有则用ROW_NUMBER()生成临时行ID（仅适用于每行对应一个JSON数组的场景）
      ROW_NUMBER() OVER () AS row_id
    FROM
      json_licensedata
    CROSS JOIN UNNEST(licensemetadata) t (license_metadata)
  )
PIVOT (
  MAX(value)
  FOR name IN ('agreementUrl', 'sellerOfRecord')
) AS p
GROUP BY row_id, "agreementUrl", "sellerOfRecord"

关键说明 #

• 必须有合适的分组键，保证同一JSON数组内的键值对被分到同一组。优先使用表中已有的唯一主键（如id），若没有则用整行数据分组。
• 若JSON数组中存在重复name，需根据业务逻辑选择聚合函数（如MAX/MIN）。

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