从Athena查询中的字典字符串中获取值

Question

我有一个包含以下两列的Athena DB表，我需要从'user_data'中获取姓名、性别和年龄

结构：

id        - string
user_date - string

数据：

id   user_data                                                        
1    {'name': 'some_name', 'gender': 'male', 'age': '36'}

user_data是字典字符串。通过将其转换为JSON:来获取值

CAST(user_data as JSON) as user_data_json

然后：

json_extract(user_data_json, '$.name') AS name

由于字典字符串是单引号的，因此CAST()失败。

还试图用双引号取代单引号：

replace(user_data,'"',"'")

然后将其转换为JSON也失败：

SYNTAX_ERROR:行1:30:无法解析列"*

如何获取这些值？

Answer 1

问题出在replace()函数上；不能用双引号代替单引号来声明文本字符串——双引号代表标识符(如列名或表名(，因此会出现错误。

要用双引号替换嵌入的单引号，可以执行以下操作：

replace(user_data, '''', '"')

您的代码的其余部分应该可以正常工作：

select 
json_extract(user_data_json, '$.name')   as name,
json_extract(user_data_json, '$.gender') as gender,
json_extract(user_data_json, '$.age')    as age
from (select id, replace(user_data, '''', '"') user_data from mytable) t