如何在Athena中将JSON结构体列按id聚合为数组？

可以通过Athena的聚合函数结合JSON处理函数来实现，具体SQL语句如下：

方法1：直接聚合为字符串数组 #

如果你的project字段是字符串类型的JSON，执行以下语句：

SELECT
  id,
  ARRAY_AGG(project) AS project
FROM your_table_name
GROUP BY id;

该语句会将同一id下的所有project字符串聚合为一个数组，输出结果类似：['{"name":"A", "status":x}', '{"name":"B", "status":y}']

方法2：输出标准JSON数组格式（匹配你的需求） #

如果需要输出不带单引号的标准JSON数组格式，结合JSON解析和格式化函数：

SELECT
  id,
  JSON_FORMAT(ARRAY_AGG(JSON_PARSE(project))) AS project
FROM your_table_name
GROUP BY id;

各函数作用说明：

• JSON_PARSE(project)：将字符串类型的project解析为JSON对象
• ARRAY_AGG(...)：按id分组，把分组内的JSON对象聚合为数组
• JSON_FORMAT(...)：将聚合后的JSON数组转换为标准的JSON字符串，得到你想要的[{"name":"A", "status":x},{"name":"B", "status":y}]格式

注意事项 #

如果你的project字段本身已经是JSON类型（而非字符串），可以省略JSON_PARSE步骤，直接使用JSON_FORMAT(ARRAY_AGG(project))即可。

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