Julian Cienfuegos Julian Cienfuegos - 1 month ago 19
SQL Question

Inner Join On Keys Listed In a List in JSON

I have a json like:

{"key": [1,2], "group": 1}
{"key": [1,3], "group": 2}


I'm loading my json into hive and I want to do an inner join with these keys on another table in the DB.

The results should be something like

Group Value
1 "this is some value corresponding to 1 in another table"
1 "this is some value corresponding to 2"
2 "this is some value corresponding to 1"
2 "this is some value corresponding to 3"


Is this a basic built in functionality, or do I need to be creative?

Answer
select      kg.group,k.value 
from                      my_json_table                                               jt
            lateral view  json_tuple(jt.my_json_column,'key','group')                 kg as key,group 
            lateral view  explode (split(regexp_extract(kg.key,'\\[(.*)\\]',1),','))  k  as value
;

https://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF

Comments