Hi
I am new to Streamsets and there are many more to learn. I am trying to convert Json to CSV having dynamic Json file.
Sample Json:
{
"order":
[
{
"order_id": "100001",
"customer_id": "1045",
"order_date": "20180701",
"ship_date": "20180701",
"item":
[
{
"product_id": "5001",
"quantity": 1
},
{
"product_id": "5021",
"quantity": 3
},
{
"product_id": "3331",
"quantity": 4
}
]
},
{
"order_id": "100002",
"customer_id": "1088",
"order_date": "20180701",
"ship_date": "20180702",
"freight": "5.00"
}
]
}
Expected Converted CSV file is:
order.order_id,order.customer_id,order.order_date,order.ship_date,order.freight,order.item.product_id,order.item.quantity
100001,1045,20180701,20180701,,5001,1
100001,1045,20180701,20180701,,5021,3
100001,1045,20180701,20180701,,3331,4
100002,1088,20180701,20180702,5,,
Approach I tried: After reading the Json file used two Field Pivoter (1. /order 2. /order/item) and Field Flattener. I am getting the output in expected csv format when JSON file has all the pivoted fields present. But for sample file as above it is throwing error in Pivoter 2 for second record (Order id=100002) and skipping that record.
Please let me know in case you need any other details for the same. Looking for the help and Thank you in advance.
Regards,
Anjalee
Best answer by Bikram
View original