How to parsing Json in AWS Athena?

user_16559228 0
Viewed 2091

I want to parse the JSON column in Athena but I have a problem in one column. The column includes an escape character. I don't know if this problem. I want to reach to Message. You can see below sample data and Message under Return. Thanks

Sample Code 
CAST(json_extract_scalar(callparameters, '$return.Message') AS VARCHAR) AS Message

This is the JSON.

{"inputs":{"usagespecid":"null","playsessionid":"null","satellitetypecd":"\"ERU\"","channelcontentid":"231"},
"return":"{\"Message\":\"Do not person you have called \"}"}    {"date":"2021-07-26","epoch":"1627"}    {"userpartyid":"23","userloginkey":"23","usercountrycode":"br","sessionkey":"23","client":{"name":"SMR","id":"1"},"usermainsatellitetype":"DS","userserviceaccountids":["23"],"usergeolocationid":"53","userpartyroleid":"76"}
amazon-athena amazon-web-services sql python json
1 Answers

If you take only the object which contains return you can parse return into json with json_parse since it contains an encoded string not an json object:

WITH dataset AS (
    SELECT * FROM (VALUES          
       (JSON '{
   "inputs":{
      "usagespecid":"null",
      "playsessionid":"null",
      "satellitetypecd":"\"ERU\"",
      "channelcontentid":"231"
   },
   "return":"{\"Message\":\"Do not person you have called \"}"
}')
 ) AS t (json_string))


SELECT json_extract_scalar(json_parse(json_extract_scalar(json_string, '$.return')), '$.Message')
FROM dataset

Output:

_col0
Do not person you have called
user_25012790
Related