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
