Why doesn't get_json_object() work to extract a value from JSON stored in a Hive SQL table?

In my hive table T the field A is a string in json format and A stores a value of {"c_e_i":"{\"e_c_f\":1}"}.

I want to get c_e_i.\"e_c_f\"

So I used get_json_object(T.A, '$.c_e_i.\\"e_c_f\\"') but it doesn't work. what should I do?

I want to get c_e_i.\"e_c_f\"

Simply because "{\"e_c_f\":1}" value is a string, not JSON map. The whole thing is not of type Map<String:Map<String:String>>, it is Map<String:String>

Map<String:Map<String:String>> should look like this {"c_e_i":{"e_c_f":1}}

And you can extract the string value like this:

with T as (
select '{"c_e_i":"{\\\"e_c_f\\\":1}"}' as A 
)

select A as original_str, 
get_json_object(A, '$.c_e_i') as result
from T

Result:

original_str	              result	

{"c_e_i":"{\"e_c_f\":1}"}     {"e_c_f":1}

You see, the result is a correct JSON Map - slashes removed as well because they are interpreted during extract and converted (removed in this case), so you can extract e_c_f from it (apply get_json_object twice):

with T as (
select '{"c_e_i":"{\\\"e_c_f\\\":1}"}' as A 
)

select A as original_str, 
get_json_object(get_json_object(A, '$.c_e_i'),'$.e_c_f') as result
from T

Result:

original_str	            result	
{"c_e_i":"{\"e_c_f\":1}"}   1

Or remove everything extra from original JSON to make it correct JSON Map<String:Map<String:String>> then extract:

with T as (
select '{"c_e_i":"{\\\"e_c_f\\\":1}"}' as A 
)

select A as original_str, 
regexp_replace(regexp_replace(regexp_replace(A, '\\\\"','"'), '"\\{','\\{'),'"\\}','\\}') as correct_JSON, --remove slashes and remove doublequotes before { and after }  
get_json_object(regexp_replace(regexp_replace(regexp_replace(A, '\\\\"','"'), '"\\{','\\{'),'"\\}','\\}'), '$.c_e_i.e_c_f') as result

Result:

original_str	                correct_json	       result	
{"c_e_i":"{\"e_c_f\":1}"}      {"c_e_i":{"e_c_f":1}}    1