CODEWITHSUNDEEP
sqljsonprestoamazon-athena
Christo Jose
Christo Jose

Reputation: 325

Get value from a dictionary string in Athena query

I have an Athena DB table with the following two columns, I need to fetch name, gender and age from the 'user_data'

Structure:

id        - string
user_date - string

Data:

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

user_data is a dictionary string. Getting the values by casting it in to JSON:

CAST(user_data as JSON) as user_data_json

Then:

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

Since the dictionary string is in single quotes the CAST() fails.

Also tried to replace single quotes with double quotes:

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

Then casting it in to JSON also failed:

SYNTAX_ERROR: line 1:30: Column ''' cannot be resolved*

How to get those values?

Upvotes: 1

Views: 4170

Answers (1)

GMB
GMB

Reputation: 222512

The problem is with your replace() function; you cannot use double quotes instead of single quotes to declare a literal string - double quotes stand for identifiers (such as column names or table names), hence the error that you are getting.

To replace embedded single quotes with double quotes, you can do:

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

The rest of your code should just work:

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

Upvotes: 1

Related Questions