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"}
Advertisement
Answer
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 |