Support Questions
Find answers, ask questions, and share your expertise
Announcements
Check out our newest addition to the community, the Cloudera Innovation Accelerator group hub.

converting nested json to rows in hive

New Contributor

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

0 REPLIES 0