How to return array<struct> from json_tuple in hive

user_12745532 0
Viewed 451

I have a hive table with json column. It's orc format and only one column has a json string.

  • json_column
{
   "type":"REGULAR",
   "period":[
      "ONCE_PER_FOUR_WEEK",
      "ONCE_PER_SIX_WEEK",
      "ONCE_PER_ONE_MONTH",
      "ONCE_PER_TWO_MONTH",
      "ONCE_PER_THREE_MONTH"
   ],
   "count":[
      "4",
      "8",
      "12"
   ],
   "day":[
      "SATURDAY",
      "SUNDAY"
   ],
   "content":[
      {
         "count":"2",
         "value":5,
         "unit":"PERCENT"
      },
      {
         "count":"3",
         "value":10,
         "unit":"PERCENT"
      }
   ]
}

I'd like to divide this column to five columns.

    type       string,
    period     array<string>,
    count      array<string>,
    day        array<string>,
    content    array<struct<count :string, value :int, unit :string>>

First, I divided this column to four columns with json_tuple.

SELECT b.type                                            as type,
       b.period                                          as period,
       b.count                                           as count,
       b.deliveryImpossibleDay                           as day,
       b.content                                         as content
FROM sample_table a
         LATERAL VIEW JSON_TUPLE(a.content, 'type', 'period', 'count', 'day',
                                 'content') b
         AS type, period, count, day, content

And I need to change content column to struct array but if returns string value.

[{"count":"2","value":5,"unit":"PERCENT"},{"count":"3","value":10,"unit":"PERCENT"}]

How can I convert it from string to array<struct<count :string, value :int, unit :string>>?

Any ideas, please?

hiveql hive arrays sql json
1 Answers

Unfortunately JSON_TUPLE and GET_JSON_OBJECT return strings. To convert JSON strings without using custom UDFs you can parse strings, split, explode and re-assemble structs and arrays.

Demo:

with sample_table as (
select '{
   "type":"REGULAR",
   "period":[
      "ONCE_PER_FOUR_WEEK",
      "ONCE_PER_SIX_WEEK",
      "ONCE_PER_ONE_MONTH",
      "ONCE_PER_TWO_MONTH",
      "ONCE_PER_THREE_MONTH"
   ],
   "count":[
      "4",
      "8",
      "12"
   ],
   "day":[
      "SATURDAY",
      "SUNDAY"
   ],
   "content":[
      {
         "count":"2",
         "value":5,
         "unit":"PERCENT"
      },
      {
         "count":"3",
         "value":10,
         "unit":"PERCENT"
      }
   ]
}' as content
)

SELECT b.type                                                 as type,
       --to convert to array<string>
       --remove [" and "], split by ","
       split(regexp_replace(b.period,'^\\["|"\\]',''),'","')  as period,
       split(regexp_replace(b.count,'^\\["|"\\]',''),'","')   as count,
       split(regexp_replace(b.day,'^\\["|"\\]',''),'","')     as day,
       --convert to struct and collect array of structs
       collect_list(named_struct('count', x.count, 'value', int(x.value), 'unit', x.unit)) as content    
FROM sample_table a
         LATERAL VIEW JSON_TUPLE(a.content, 'type', 'period', 'count', 'day', 'content') b AS type, period, count, day, content
         LATERAL VIEW explode(split(regexp_replace(b.content,'^\\[|\\]$',''), --remove []
                          '(?<=\\}),(?=\\{)' --split by comma only after } and before {
                         )) e as str_struct
         LATERAL VIEW JSON_TUPLE(e.str_struct,'count','value', 'unit') x as count, value, unit
group by b.type,
       b.period,
       b.count,
       b.day

Result:

type     period                                                                                                     count           day                     content
REGULAR ["ONCE_PER_FOUR_WEEK","ONCE_PER_SIX_WEEK","ONCE_PER_ONE_MONTH","ONCE_PER_TWO_MONTH","ONCE_PER_THREE_MONTH"] ["4","8","12"]  ["SATURDAY","SUNDAY"]   [{"count":"2","value":5,"unit":"PERCENT"},{"count":"3","value":10,"unit":"PERCENT"}]
user_27003440
Related