I am facing a problem, transforming a very complex nested JSON using jolt transformation. Input and output detail is given below.
Input
[
{
"metadata": {
"event_time": "2023-09-04T08:46:15.0842Z"
},
"payload": [
{
"plant": "0DE1",
"store": "",
"t_objectandcharvalcomb": [
{
"productalloccharacteristic01": "0DE1",
"productalloccharacteristic02": "30",
"productalloccharacteristic03": "FC",
"productalloccharacteristic04": "#",
"t_time_series_consumption": [
{
"startdateofproductallocperd": "2023-09-04",
"prodallocconsumpnegopenqty": 0,
"prodallocconsumpopenqty": 0,
"prodallocconsumpassigqty": 0,
"prodallocconsumpqty": 0,
"prodallocconsumpqtyunit": "EA"
},
{
"startdateofproductallocperd": "2023-09-05",
"prodallocconsumpnegopenqty": 0,
"prodallocconsumpopenqty": 0,
"prodallocconsumpassigqty": 0,
"prodallocconsumpqty": 0,
"prodallocconsumpqtyunit": "EA"
}
]
}
]
},
{
"plant": "0DE1",
"store": "",
"t_objectandcharvalcomb": [
{
"productalloccharacteristic01": "0DE1",
"productalloccharacteristic02": "30",
"productalloccharacteristic03": "LT",
"t_time_series_consumption": [
{
"startdateofproductallocperd": "2023-09-04",
"prodallocconsumpnegopenqty": 0,
"prodallocconsumpopenqty": 0,
"prodallocconsumpassigqty": 0,
"prodallocconsumpqty": 0,
"prodallocconsumpqtyunit": "EA"
},
{
"startdateofproductallocperd": "2023-09-05",
"prodallocconsumpnegopenqty": 0,
"prodallocconsumpopenqty": 0,
"prodallocconsumpassigqty": 0,
"prodallocconsumpqty": 0,
"prodallocconsumpqtyunit": "EA"
}
]
}
]
},
{
"plant": "0DE1",
"store": "",
"t_objectandcharvalcomb": [
{
"productalloccharacteristic01": "0DE1",
"productalloccharacteristic02": "30",
"productalloccharacteristic03": "PP",
"productalloccharacteristic04": "#",
"t_time_series_consumption": [
{
"startdateofproductallocperd": "2023-09-04",
"startdateofprodallocconsump": "2023-09-04",
"prodallocconsumpnegopenqty": 0,
"prodallocconsumpopenqty": 0,
"prodallocconsumpassigqty": 0,
"prodallocconsumpqty": 0,
"prodallocconsumpqtyunit": "EA"
},
{
"startdateofproductallocperd": "2023-09-05",
"prodallocconsumpnegopenqty": 0,
"prodallocconsumpopenqty": 0,
"prodallocconsumpassigqty": 0,
"prodallocconsumpqty": 0,
"prodallocconsumpqtyunit": "EA"
}
]
}
]
}
]
}
]
Output Expected
[
{
"event_time": "2023-09-04T08:46:15.0842Z",
"Plant": "0DE1",
"Store": "",
"Location": "0DEA",
"Distribution_Channel": "#",
"Packing_type": "FC",
"Product_Division": "",
"Date": "2023-09-04",
"Planned_Capacity_qty": 0,
"Consumed_Capacity_qty": 0,
"Negative_open_qty": 0,
"Consumed_assign_qty": 0
},
{
"event_time": "2023-09-04T08:46:15.0842Z",
"Plant": "0DE1",
"Store": "",
"Location": "0DEA",
"Distribution_Channel": "#",
"Packing_type": "FC",
"Product_Division": "",
"Date": "2023-09-05",
"Planned_Capacity_qty": 0,
"Consumed_Capacity_qty": 0,
"Negative_open_qty": 0,
"Consumed_assign_qty": 0
},
{
"event_time": "2023-09-04T08:46:15.0842Z",
"Plant": "0DE1",
"Store": "",
"Location": "0DEA",
"Distribution_Channel": "#",
"Packing_type": "LT",
"Product_Division": "",
"Date": "2023-09-04",
"Planned_Capacity_qty": 0,
"Consumed_Capacity_qty": 0,
"Negative_open_qty": 0,
"Consumed_assign_qty": 0
},
{
"event_time": "2023-09-04T08:46:15.0842Z",
"Plant": "0DE1",
"Store": "",
"Location": "0DEA",
"Distribution_Channel": "#",
"Packing_type": "LT",
"Product_Division": "",
"Date": "2023-09-05",
"Planned_Capacity_qty": 0,
"Consumed_Capacity_qty": 0,
"Negative_open_qty": 0,
"Consumed_assign_qty": 0
},
{
"event_time": "2023-09-04T08:46:15.0842Z",
"Plant": "0DE1",
"Store": "",
"Location": "0DEA",
"Distribution_Channel": "#",
"Packing_type": "PP",
"Product_Division": "",
"Date": "2023-09-04",
"Planned_Capacity_qty": 0,
"Consumed_Capacity_qty": 0,
"Negative_open_qty": 0,
"Consumed_assign_qty": 0
},
{
"event_time": "2023-09-04T08:46:15.0842Z",
"Plant": "0DE1",
"Store": "",
"Location": "0DEA",
"Distribution_Channel": "#",
"Packing_type": "PP",
"Product_Division": "",
"Date": "2023-09-05",
"Planned_Capacity_qty": 0,
"Consumed_Capacity_qty": 0,
"Negative_open_qty": 0,
"Consumed_assign_qty": 0
}
]
Jolt spec which i m using
[
{
"operation": "shift",
"spec": {
"*": {
"payload": {
"*": {
"t_objectandcharvalcomb": {
"*": {
"t_time_series_consumption": {
"*": {
"@(6,metadata.event_time)": "[&1].event_time",
"@(4,plant)": "[&1].Plant",
"@(4,store)": "[&1].Store",
"@(2,productalloccharacteristic01)": "[&1].Location",
"@(2,productalloccharacteristic02)": "[&1].Distribution_Channel",
"@(2,productalloccharacteristic03)": "[&1].Packing_type",
"@(2,productalloccharacteristic04)": "[&1].Product_Division",
"startdateofproductallocperd": "[&1].Date",
"productallocquantity": "[&1].Planned_Capacity_qty",
"prodallocconsumpopenqty": "[&1].Consumed_Capacity_qty",
"prodallocconsumpnegopenqty": "[&1].Negative_open_qty",
"prodallocconsumpassigqty": "[&1].Consumed_assign_qty"
}
}
}
}
}
}
}
}
}
]
But its not coming as expected.
Pls help Can anyone who is a jolt expert, help me get the desired output. I think i m stuck in the last step
Need to add one more partition which might be provided by adding &5 along with the current identifier &1, which has not to be nested within square brackets, such as
[
{
"operation": "shift",
"spec": {
"*": {
"payload": {
"*": {
"t_objectandcharvalcomb": {
"*": {
"t_time_series_consumption": {
"*": {
"@6,metadata.event_time": "&5_&1.event_time",
"@4,plant": "&5_&1.Plant",
"@4,store": "&5_&1.Store",
"@2,productalloccharacteristic01": "&5_&1.Location",
"@2,productalloccharacteristic02": "&5_&1.Distribution_Channel",
"@2,productalloccharacteristic03": "&5_&1.Packing_type",
"@2,productalloccharacteristic04": "&5_&1.Product_Division",
"startdateofproductallocperd": "&5_&1.Date",
"productallocquantity": "&5_&1.Planned_Capacity_qty",
"prodallocconsumpopenqty": "&5_&1.Consumed_Capacity_qty",
"prodallocconsumpnegopenqty": "&5_&1.Negative_open_qty",
"prodallocconsumpassigqty": "&5_&1.Consumed_assign_qty"
}
}
}
}
}
}
}
}
},
{ // get rid of the object keys
"operation": "shift",
"spec": {
"*": ""
}
}
]