Reply
New Contributor
Posts: 2
Registered: ‎12-18-2018

Explode Array of Structs using get_json_object in hive

 

Hi,

I have one column in hive table wherein I have stored entire json data map as string. I am using get_json_object to fetch each element of json. However I have one element which is array of structs. I need to explode that array of structs.

Event Sample:

 

  1. {"evtDataMap":{"ucmEvt":{"rscDrvdStateEntMap":[{"prov_Id":"OCP","acct_Id":"ACCOUNT_0","chanlTypeId":"Chat","derivedAvlFlg":"TRUE","activeWrkCnt":"0","avlState":"READY","workLimit":"2"},{"providerId":"OCP Email","accountId":"ACCOUNT_0","channelTypeId":"Email","derivedAvailableFlag":"TRUE","activeWorkCount":"0","availabilityState":"READY","workLimit":"2"},{"providerId":"OCP ShortMessageService","accountId":"ACCOUNT_0","channelTypeId":"ShortMessageService","derivedAvailableFlag":"TRUE","activeWorkCount":"0","availabilityState":"READY","workLimit":"2"}]}}}

 

 

Entire evtDataMap is stored in a hive column and I want the output like

prov_idacct_IdchanlTypeIdderivedAvlFlgactiveWrkCntavlStateworkLimit
OCPACCOUNT_0ChatTRUE0READY2
OCP EmailACCOUNT_0EmailTRUE0READY2
OCP ShortMessageServiceACCOUNT_0hortMessageServicTRUE0READY2

Basically I want to flatten the array of structs.

I am using query like this

 

  1. with temp as (
  2. select evName,get_json_object(evtDataMap,'$.ucmEvt.rscDrvdStateEntMap') as mapp from avaya.jmsrec_temp where evtName ='USER')
  3. Select evtName, a.prov_id,a.acct_Id,a.chanlTypeId ,a.derivedAvlFlg,a.activeWrkCnt,a.avlState,a.workLimit from temp lateral view inline(temp.mapp) a as prov_id,acct_Id,chanlTypeId ,derivedAvlFlg,activeWrkCnt,avlState,workLimit

 eventName is a hive table column.I am getting an error as

 

 

"FAILED: UDFArgumentException Top level object must be an array but was string"

Tried array over get_json_object but still I am getting an error

 

  1. array(get_json_object(eventDataMap,'$.ucmEvent.user.resourceDerivedStateEntryMap')) as mapp
 Error FAILED: UDFArgumentException The sub element must be struct, but was string

I am stuck with this issue since 3 days, any help is much much appreciated..