Escaping child operator in the HIVE function get_json_object

Question

I have table with a string column which contains data in JSON format. Then, I'm using the function get_json_object to parse some data from it. The problem is that one of my fields contains a . in its name. So, if write the field name in plain text, the function will interpret it like a child operator.

Here is a sample code that works:

select get_json_object(col, '$.rootkey')
from (select '{"rootkey":15,"key.a":16,"key.b":17}' as col) test;

But this one fails:

select get_json_object(col, '$.key.a')
from (select '{"rootkey":15,"key.a":16,"key.b":17}' as col) test;

Is there any way escape . in the get_json_object function?

user_2700344 · Answer

Currently get_json_object() does not support escaping, see HIVE-2927

The workaround is to use lateral view json_tuple():

select v.rootkey, v.key_a, v.key_b
  from (select '{"rootkey":15,"key.a":16,"key.b":17}' as col) test
       lateral view json_tuple(col, 'rootkey', 'key.a','key.b') v as rootkey, key_a, key_b
;

Result:

v.rootkey   v.key_a   v.key_b   
15          16        17

Escaping child operator in the HIVE function get_json_object

1 Answers