CODEWITHSUNDEEP
hivehiveql
Shan
Shan

Reputation: 53

Json - Flatten Key and Values in Hive

In a Hive table having a record JSON column value as: {"XXX": ["123","456"],"YYY": ["246","135"]} and ID as ABC

Need to flatten it as below in Hive query.

Key Value ID
XXX 123 ABC
XXX 456 ABC
YYY 246 ABC
YYY 135 ABC

Upvotes: 0

Views: 388

Answers (1)

ggordon
ggordon

Reputation: 10035

The following uses get_json_object to extract json keys before using regexp_replace and split to convert the remaining values to arrays. With the assistance of explode and lateral views from the resulting subquery, the data has been extracted. The full reproducible example is below:

WITH input_df AS (
    SELECT '{"XXX": ["123","456"],"YYY": ["246","135"]}' my_col
)
SELECT
    t.key,
    kv.kval as value
FROM (
    SELECT
         explode(map(
             'XXX',
             split(regexp_replace(get_json_object(my_col,'$.XXX'),'"|\\[|\\]',''),','),
             'YYY',
             split(regexp_replace(get_json_object(my_col,'$.YYY'),'"|\\[|\\]',''),',')
         )) 
    FROM
        input_df
) t LATERAL VIEW explode(t.value) kv as kval

You may use the query below if your table/view is named input_df and your json column is my_col

SELECT
    t.key,
    kv.kval as value
FROM (
    SELECT
         explode(map(
             'XXX',
             split(regexp_replace(get_json_object(my_col,'$.XXX'),'"|\\[|\\]',''),','),
             'YYY',
             split(regexp_replace(get_json_object(my_col,'$.YYY'),'"|\\[|\\]',''),',')
         )) 
    FROM
        input_df
) t LATERAL VIEW explode(t.value) kv as kval

Response To Updated Question 1:

SELECT
    t.key,
    kv.kval as value,
    'ABC' as ID
FROM (
    SELECT
         explode(map(
             'XXX',
             split(regexp_replace(get_json_object(my_col,'$.XXX'),'"|\\[|\\]',''),','),
             'YYY',
             split(regexp_replace(get_json_object(my_col,'$.YYY'),'"|\\[|\\]',''),',')
         )) 
    FROM
        input_df
) t LATERAL VIEW explode(t.value) kv as kval

Let me know if this works for you.

Upvotes: 2

Related Questions