I have this table (2 columns) with a json field (cart_items): Column 1: order_id: 1234 Column 2 (json): { "rows": [{ "id": "13422992", "price": "40.18952581520672", "quantity": 1, "name": "product 1", "itemIndex": 1, "basicItemData": { "name": "product 1", "quantity": 1, "type": "digital", "price": { "amountLocalCurrency": "35.82", "currency": "EUR" }, "productId": "13422992", "category": "APPAREL" }, "itemSpecificData": { "physicalGoods": { "size": "M", "color": "010", "weight": 0, "brand": "mybrand", "condition": "NEW", "wrapAsGift": false, "removeReceipt": false, "customDesign": false, "prepayCustomsTaxesAndFees": false, "style": "5626" } } }, { "id": "13203164", "price": "22.596790896657268", "quantity": 1, "name": "product 2", "itemIndex": 2, "basicItemData": { "name": "product 2", "quantity": 1, "type": "physical", "price": { "amountLocalCurrency": "20.14", "currency": "EUR" }, "productId": "13203164", "category": "APPAREL" }, "itemSpecificData": { "physicalGoods": { "size": "M", "color": "645", "weight": 0, "brand": "mybrand", "condition": "NEW", "wrapAsGift": false, "removeReceipt": false, "customDesign": false, "prepayCustomsTaxesAndFees": false, "style": "6509" } } }] } I have been trying the following query: select trans_id ,get_json_object (cart_items,concat('$.rows[',e.i,'].quantity')) as quantity ,get_json_object (cart_items,concat('$.rows[',e.i,'].name')) as name ,get_json_object (cart_items,concat('$.rows.basicItemData[',f.g,'].type')) as type from My_Table lateral view posexplode (split(get_json_object (cart_items,'$.rows[*].name'),',')) e as i,x lateral view posexplode (split(get_json_object (cart_items,'$.rows.basicItemData[*].name'),'},')) f as g,k where order_id='1234' ===================================================== Outcome of this is: Order_id.      Quantity.         Name.         Type 1234.              1.                product 1      null 1234.              1.                product 2      null As you see, I get the first columns right but the 'Type' column shows null, obviously there's something wrong with my query. Any help is appreciated. Thanks Abel ... View more

Hope you can help me with the following issue (my hive knowledge is limited) I have the following json array in one of my tables: {"rows":[{"id":"13358958","price":"37.89","basicItemData":{"name":"Prod1 ","price":{"Price.Loc":"27.95","curr":"GBP"},"productId":"13358958","ctg":"physical"},"itemSpecificData":{"physicalGoods":{"size":"M","style":"12345"}}},{"id":"13092564","price":"57.99","basicItemData":{"name":"Prod2","price":{"Price.Loc":"27.95","curr":"GBP"},"productId":"13092564","ctg":"APPAREL"},"itemSpecificData":{"physicalGoods":{"size":"S","style":"54321"}}}]} Expecting outcome: Customer. ID. Price. Name. Price.Loc. Curr. productid. ctg. size. style Cst1 13358958. 37.89. prod1. 27.95. GBP. 13358958. physical. M. 12345 Cst1 13092564. 57.99. prod2. 47.95. GBP. 13358958. APPAREL . M. 54321 However when I query this on hive using following: select customer, explode(array(virtual_table)) from mytable  L ATERAL VIEW explode(array(cart_items)) vl AS virtual_table where customer='cst1' I don't get the rows: data is not split. and if I remove 'array' from the query, I get an error: Can't extract value from cart_items#59648: need struct type but got string. Any help/suggestion is appreciated. Abdel ... View more