rcongiu
commented
8 years ago It looks like in the struct the 'section' field is missing, it should be
section:array<struct<currentAmount:string,sectionDescription:string>>
I suggest you use this tool https://github.com/quux00/hive-json-schema to create your schema from JSON.
If you use it, the table should be `CREATE TABLE x ( accountlevelcharges struct<noofcharges:int, sections:array<struct<currentamount:string, sectiondescription:string>>>) ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe';'
You can find the size of the array as
select size(accountlevelcharges.sections) from x;
I am not quite sure what you're trying to achieve, but you can do something like:
You can EXPLODE the array:
select accountlevelcharges.noofcharges, als.* from x lateral view explode( accountlevelcharges.sections) als as alss where alss.sectiondescription = 'total';
OK 2 {"currentamount":"8","sectiondescription":"total"}
rajkumarsowmy
I have this column in hive table.
accountLevelCharges struct<noOfCharges:int,sections:array<struct<noOfCharges:int, noOfChanges:int,changeIndicator:string, currentAmount:string, prevAmount:string, differenceAmount:string, sectionType:string, sectionDescription:string>>, noOfChanges:int, changeIndicator:string, currentAmount:string, prevAmount:string, differenceAmount:string, chargeType:string>,
this is my json { "accountLevelCharges": { "noOfCharges": 2, "sections": [{ "currentAmount": "7", "sectionDescription": "Subtotal" }, { "currentAmount": "8", "sectionDescription": "total" }] } } i have array of structures in sections array and
how do i know from which block of the array im getting the amount, i can do based on sectionDescription. can i query like select accountLevelCharges.section from table where accountLevelCharges.section.sectionDesc='total'?
since array element is dynamic, how do i know the size of the array. or can i do size of the sections and see if a particular element is present and then select that one based on search criteria?
please provide your suggestions? Thanks and appreciate your time.