Databricks SQL - extract data from nested JSON text

 

%sql

select distinct left(v2.inner_key, locate('_', v2.inner_key) - 1 ) as some_alias

from db_name.tbl_name 

     LATERAL VIEW json_tuple(tbl_name.nested_json_col_name, 'outter_key') v1 

     as outer_key

     LATERAL VIEW json_tuple(v1.outer_key, 'inner_key') v2

     as inner_key