为什么 get_json_object() 无法从存储在 Hive SQL 表中的 JSON 中提取值

很简单，因为"{\"e_c_f\":1}"值是一个字符串，而不是JSON映射.整个事情不是Map<String:Map<String:String>>型的，而是Map<String:String>型的

Map<String:Map<String:String>>应该是这样的{"c_e_i":{"e_c_f":1}}

您可以像这样提取字符串值:

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

结果:

original_str                  result    

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

您可以看到，结果是正确的JSON映射-斜杠也被删除了，因为它们在提取和转换过程中被解释(在本例中是删除的)，所以您可以从中提取e_c_f(应用两次get_json_Object):

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

结果:

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

或者删除原始JSON中多余的所有内容以使其正确JSON Map<String:Map<String:String>>，然后解压缩:

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

结果:

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