AWS Athena json_extract query from string field returns empty values

Question

I have in Athena json string:

{"recurrent_jobs.new_page.career_level.trainee":0,"recurrent_jobs.new_page.career_level.assistant":1}

I need get result: trainee=0

I make a query:

select 
     json_extract(
     '{"recurrent_jobs.new_page.career_level.trainee":0,"recurrent_jobs.new_page.career_level.assistant":1}', 
     '$.recurrent_jobs.new_page.career_level.trainee')   

And I have a empty result. I think the problem is mit dots. What can I do?

Answer 1

'$.recurrent_jobs.new_page.career_level.trainee' represents path to property of deeply nested object, something like the following:

{
   "recurrent_jobs":{
      "new_page":{
         "career_level":{
            "trainee":0
         }
      }
   }
}

You need to escape the property name with dots - '$["recurrent_jobs.new_page.career_level.trainee"]':

select json_extract(
    '{
        "recurrent_jobs.new_page.career_level.trainee":0,
        "recurrent_jobs.new_page.career_level.assistant":1
    }',
    '$["recurrent_jobs.new_page.career_level.trainee"]');

Output:

_col0
0