如何在AWS Athena中转换Game表的JSONB格式数据？

针对你需求的JSON结构转换，结合Athena基于Presto引擎的特性，可以用以下SQL实现：

SELECT
  json_build_object(
    'Games', json_build_object(
      'AllGames', array_agg(
        json_build_object(
          entry.key, array[cast(json_format(entry.value) AS varchar)]
        )
      )
    )
  ) AS converted_history
FROM
  "Game",
  UNNEST(MAP_ENTRIES(json_parse(history)->'Games')) AS t(entry)
WHERE
  history IS NOT NULL AND json_parse(history)->'Games' IS NOT NULL;

代码说明： #

1. 拆解JSON键值对：用MAP_ENTRIES(json_parse(history)->'Games')把Games下的JSON对象转换成键值对的map，再通过UNNEST将每个键值对拆成单独的行，对应Postgres里的jsonb_each逻辑。
2. 值转字符串并包装数组：json_format(entry.value)会把任何类型的JSON值（数字、字符串等）统一转成带引号的字符串格式，比如数字value2会变成"value2"，再用array[]包装成单元素数组，最后通过json_build_object把键和数组重新组合成单个对象。
3. 聚合与外层包装：array_agg把所有单个对象聚合成数组，再通过两层json_build_object构建最终的外层Games结构。
4. 空值过滤：WHERE子句过滤掉history为空或Games节点为空的记录，和你Postgres代码的适用场景保持一致。

验证示例： #

如果原history值为{"Games": {"key1": "value1", "key2": 123}}，执行后会得到：

{"Games": {"AllGames": [{"key1": ["value1"]}, {"key2": ["123"]}]}}

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