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
