如何从AWS Athena获取带键名的标准JSON格式查询结果？

问题描述 #

对象obj的结构如下：

UUID              string 
MobileCountryCode string 
UserState         []struct {
    Mobile    int64 
    IsNewUser bool  
} 

直接执行查询：

select obj FROM table_name

得到的输出是非规范格式，键名无引号、用=替代:：

{uuid=UCLPKJWPZXH,mobile_country_code=,user_state=[{mobile=9988998899, is_new_user=false}]}

尝试用CAST转换为JSON：

select CAST(obj AS JSON) AS json_obj FROM table_name

结果仅保留值，丢失了键名：

["UCLPKJWPZXH","",[["9988998899",false]]]

需要的标准JSON格式为：

{"uuid":"UCLPKJWPZXH","mobile_country_code":"","user_state":[{"mobile":9988998899,"is_new_user":false}]}

解决方案 #

使用Athena支持的Presto SQL函数手动构造规范JSON对象，查询语句如下：

SELECT json_format(
  json_build_object(
    'uuid', obj.uuid,
    'mobile_country_code', obj.mobile_country_code,
    'user_state', transform(obj.user_state, s -> json_build_object('mobile', s.mobile, 'is_new_user', s.is_new_user))
  )
) AS json_obj
FROM table_name

语句说明 #

• json_build_object：用于构造顶层JSON对象，明确指定每个键名（如'uuid'）和对应的字段值。
• transform：遍历user_state数组中的每个元素，为每个子结构构造包含对应键值对的JSON对象。
• json_format：将构造好的JSON结构转换为标准字符串格式，确保输出符合JSON规范（带双引号、冒号分隔）。

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