I am facing a problem, transforming a very complex array JSON input to a flat JSON format. The Input, Jolt Transformation & output detail is given below.
Input :
{
"guid": 16161616,
"WebHookId": 1234567890,
"siteLocation": "offshore",
"ReferenceCountry": [
{
"RefCountry": "DE",
"Country": "DE"
},
{
"RefCountry": "DE",
"Country": "BE"
},
{
"RefCountry": "DE",
"Country": "NL"
}
],
"WTGLevel": [
{
"WTG_SCID": "/",
"WTG_ReferenceCountry": "DE",
"WTG_Material": "WTG_01",
"WTG_Description": "CS-048948A-#01",
"WTG_Quantity": 1,
"WTG_UnitOfMeasure": "EA",
"WTG_WTGType": "V136-4.0MW",
"WTG_RotorDiameter": "136 m",
"WTG_MarkRelease": "3F",
"WTG_NominalEffect": 4200,
"WTG_HubHeight": 112,
"component": [
{
"Cmpt_Material": "SP_AVI_LAMP_01",
"Cmpt_Description": "Site Parts Aviation Lamp MSI",
"Cmpt_Quantity": 1,
"Cmpt_UnitOfMeasure": "EA",
"Cmpt_CapacityType": "NAC_OPT",
"Cmpt_MaterialGroup": 5,
"Cmpt_DerivedMaterial": 29197892,
"Cmpt_MaterialIndicator": "VAR",
"Cmpt_PriceBaseID": "P/AV_LA:AVWTG_3MW/AV_LA_18/AV_Q_1"
},
{
"Cmpt_Material": "LAMP_01",
"Cmpt_Description": "Lamp MSI",
"Cmpt_Quantity": 5,
"Cmpt_UnitOfMeasure": "fA",
"Cmpt_CapacityType": "OPT",
"Cmpt_MaterialGroup": 51,
"Cmpt_DerivedMaterial": 88899892,
"Cmpt_MaterialIndicator": "VAR",
"Cmpt_PriceBaseID": "AV_LA_18/AV_Q_1"
}
]
},
{
"WTG_SCID": "zyUL6lemDdM",
"WTG_ReferenceCountry": "DE",
"WTG_Material": "WTG_01",
"WTG_Description": "CS-042692A-#01",
"WTG_Quantity": 1,
"WTG_UnitOfMeasure": "EA",
"WTG_WTGType": "V136-4.0MW",
"WTG_RotorDiameter": "136 m",
"WTG_MarkRelease": "3E",
"WTG_NominalEffect": 4.2,
"WTG_HubHeight": 112,
"component": [
{
"Cmpt_Material": "DRIVE_TRAIN_01",
"Cmpt_Description": "Drive Train MSI",
"Cmpt_Quantity": 1,
"Cmpt_UnitOfMeasure": "EA",
"Cmpt_CapacityType": "DT_V136_4.0ME_3E",
"Cmpt_MaterialGroup": 540,
"Cmpt_DerivedMaterial": 29219265,
"Cmpt_MaterialIndicator": "VAR",
"Cmpt_PriceBaseID": "P/DT:P_3MW/MK_3E/R_136M/LOC_01"
}
]
}
]
}
Jolt Code :
[
{
"operation": "shift",
"spec": {
"WTGLevel": {
"*": {
"component": {
"*": { // partition the objects by indexes from two different levels
"@4,guid": "&3_&1.guid",
"@4,WebHookId": "&3_&1.WebHookId",
"@4,siteLocation": "&3_&1.siteLocation",
"@4,ReferenceCountry.RefCountry": "&3_&1.&",
"@4,ReferenceCountry.Country": "&3_&1.&",
"@2,WTG_SCID": "&3_&1.WTG_SCID",
"@2,WTG_ReferenceCountry": "&3_&1.WTG_ReferenceCountry",
"@2,WTG_Material": "&3_&1.WTG_Material",
"@2,WTG_Description": "&3_&1.WTG_Description",
"@2,WTG_Quantity": "&3_&1.WTG_Quantity",
"@2,WTG_UnitOfMeasure": "&3_&1.WTG_UnitOfMeasure",
"@2,WTG_WTGType": "&3_&1.WTG_WTGType",
"@2,WTG_RotorDiameter": "&3_&1.WTG_RotorDiameter",
"@2,WTG_MarkRelease": "&3_&1.WTG_MarkRelease",
"@2,WTG_NominalEffect": "&3_&1.WTG_NominalEffect",
"@2,WTG_HubHeight": "&3_&1.WTG_HubHeight",
"Cmpt_Material|Cmpt_Quantity|Cmpt_UnitOfMeasure|Cmpt_Description": "&3_&1.&",
"Cmpt_MaterialGroup|Cmpt_DerivedMaterial|Cmpt_MaterialIndicator|CapacityType": "&3_&1.&"
}
}
}
}
}
},
{ // add an array wrapper at the topmost level while getting rid of newly formed object labels
"operation": "shift",
"spec": {
"*": "[]"
}
}
]
Excepted Output : 9 rows
[
{
"guid": 16161616,
"WebHookId": 1234567890,
"siteLocation": "offshore",
"RefCountry": "DE",
"Country": "DE",
"WTG_SCID": "/",
"WTG_ReferenceCountry": "DE",
"WTG_Material": "WTG_01",
"WTG_Description": "CS-048948A-#01",
"WTG_Quantity": 1,
"WTG_UnitOfMeasure": "EA",
"WTG_WTGType": "V136-4.0MW",
"WTG_RotorDiameter": "136 m",
"WTG_MarkRelease": "3F",
"WTG_NominalEffect": 4200,
"WTG_HubHeight": 112,
"Cmpt_Material": "SP_AVI_LAMP_01",
"Cmpt_Quantity": 1,
"Cmpt_UnitOfMeasure": "EA",
"Cmpt_Description": "Site Parts Aviation Lamp MSI",
"Cmpt_MaterialGroup": 5,
"Cmpt_DerivedMaterial": 29197892,
"Cmpt_MaterialIndicator": "VAR"
},
{
"guid": 16161616,
"WebHookId": 1234567890,
"siteLocation": "offshore",
"RefCountry": "DE",
"Country": "DE",
"WTG_SCID": "/",
"WTG_ReferenceCountry": "DE",
"WTG_Material": "WTG_01",
"WTG_Description": "CS-048948A-#01",
"WTG_Quantity": 1,
"WTG_UnitOfMeasure": "EA",
"WTG_WTGType": "V136-4.0MW",
"WTG_RotorDiameter": "136 m",
"WTG_MarkRelease": "3F",
"WTG_NominalEffect": 4200,
"WTG_HubHeight": 112,
"Cmpt_Material": "LAMP_01",
"Cmpt_Quantity": 5,
"Cmpt_UnitOfMeasure": "fA",
"Cmpt_Description": "Lamp MSI",
"Cmpt_MaterialGroup": 51,
"Cmpt_DerivedMaterial": 88899892,
"Cmpt_MaterialIndicator": "VAR"
},
{
"guid": 16161616,
"WebHookId": 1234567890,
"siteLocation": "offshore",
"RefCountry": "DE",
"Country": "DE",
"WTG_SCID": "zyUL6lemDdM",
"WTG_ReferenceCountry": "DE",
"WTG_Material": "WTG_01",
"WTG_Description": "CS-042692A-#01",
"WTG_Quantity": 1,
"WTG_UnitOfMeasure": "EA",
"WTG_WTGType": "V136-4.0MW",
"WTG_RotorDiameter": "136 m",
"WTG_MarkRelease": "3E",
"WTG_NominalEffect": 4.2,
"WTG_HubHeight": 112,
"Cmpt_Material": "DRIVE_TRAIN_01",
"Cmpt_Quantity": 1,
"Cmpt_UnitOfMeasure": "EA",
"Cmpt_Description": "Drive Train MSI",
"Cmpt_MaterialGroup": 540,
"Cmpt_DerivedMaterial": 29219265,
"Cmpt_MaterialIndicator": "VAR"
},
{
"guid": 16161616,
"WebHookId": 1234567890,
"siteLocation": "offshore",
"RefCountry": "DE",
"Country": "BE",
"WTG_SCID": "/",
"WTG_ReferenceCountry": "DE",
"WTG_Material": "WTG_01",
"WTG_Description": "CS-048948A-#01",
"WTG_Quantity": 1,
"WTG_UnitOfMeasure": "EA",
"WTG_WTGType": "V136-4.0MW",
"WTG_RotorDiameter": "136 m",
"WTG_MarkRelease": "3F",
"WTG_NominalEffect": 4200,
"WTG_HubHeight": 112,
"Cmpt_Material": "SP_AVI_LAMP_01",
"Cmpt_Quantity": 1,
"Cmpt_UnitOfMeasure": "EA",
"Cmpt_Description": "Site Parts Aviation Lamp MSI",
"Cmpt_MaterialGroup": 5,
"Cmpt_DerivedMaterial": 29197892,
"Cmpt_MaterialIndicator": "VAR"
},
{
"guid": 16161616,
"WebHookId": 1234567890,
"siteLocation": "offshore",
"RefCountry": "DE",
"Country": "BE",
"WTG_SCID": "/",
"WTG_ReferenceCountry": "DE",
"WTG_Material": "WTG_01",
"WTG_Description": "CS-048948A-#01",
"WTG_Quantity": 1,
"WTG_UnitOfMeasure": "EA",
"WTG_WTGType": "V136-4.0MW",
"WTG_RotorDiameter": "136 m",
"WTG_MarkRelease": "3F",
"WTG_NominalEffect": 4200,
"WTG_HubHeight": 112,
"Cmpt_Material": "LAMP_01",
"Cmpt_Quantity": 5,
"Cmpt_UnitOfMeasure": "fA",
"Cmpt_Description": "Lamp MSI",
"Cmpt_MaterialGroup": 51,
"Cmpt_DerivedMaterial": 88899892,
"Cmpt_MaterialIndicator": "VAR"
},
{
"guid": 16161616,
"WebHookId": 1234567890,
"siteLocation": "offshore",
"RefCountry": "DE",
"Country": "BE",
"WTG_SCID": "zyUL6lemDdM",
"WTG_ReferenceCountry": "DE",
"WTG_Material": "WTG_01",
"WTG_Description": "CS-042692A-#01",
"WTG_Quantity": 1,
"WTG_UnitOfMeasure": "EA",
"WTG_WTGType": "V136-4.0MW",
"WTG_RotorDiameter": "136 m",
"WTG_MarkRelease": "3E",
"WTG_NominalEffect": 4.2,
"WTG_HubHeight": 112,
"Cmpt_Material": "DRIVE_TRAIN_01",
"Cmpt_Quantity": 1,
"Cmpt_UnitOfMeasure": "EA",
"Cmpt_Description": "Drive Train MSI",
"Cmpt_MaterialGroup": 540,
"Cmpt_DerivedMaterial": 29219265,
"Cmpt_MaterialIndicator": "VAR"
},
{
"guid": 16161616,
"WebHookId": 1234567890,
"siteLocation": "offshore",
"RefCountry": "DE",
"Country": "NL",
"WTG_SCID": "/",
"WTG_ReferenceCountry": "DE",
"WTG_Material": "WTG_01",
"WTG_Description": "CS-048948A-#01",
"WTG_Quantity": 1,
"WTG_UnitOfMeasure": "EA",
"WTG_WTGType": "V136-4.0MW",
"WTG_RotorDiameter": "136 m",
"WTG_MarkRelease": "3F",
"WTG_NominalEffect": 4200,
"WTG_HubHeight": 112,
"Cmpt_Material": "SP_AVI_LAMP_01",
"Cmpt_Quantity": 1,
"Cmpt_UnitOfMeasure": "EA",
"Cmpt_Description": "Site Parts Aviation Lamp MSI",
"Cmpt_MaterialGroup": 5,
"Cmpt_DerivedMaterial": 29197892,
"Cmpt_MaterialIndicator": "VAR"
},
{
"guid": 16161616,
"WebHookId": 1234567890,
"siteLocation": "offshore",
"RefCountry": "DE",
"Country": "NL",
"WTG_SCID": "/",
"WTG_ReferenceCountry": "DE",
"WTG_Material": "WTG_01",
"WTG_Description": "CS-048948A-#01",
"WTG_Quantity": 1,
"WTG_UnitOfMeasure": "EA",
"WTG_WTGType": "V136-4.0MW",
"WTG_RotorDiameter": "136 m",
"WTG_MarkRelease": "3F",
"WTG_NominalEffect": 4200,
"WTG_HubHeight": 112,
"Cmpt_Material": "LAMP_01",
"Cmpt_Quantity": 5,
"Cmpt_UnitOfMeasure": "fA",
"Cmpt_Description": "Lamp MSI",
"Cmpt_MaterialGroup": 51,
"Cmpt_DerivedMaterial": 88899892,
"Cmpt_MaterialIndicator": "VAR"
},
{
"guid": 16161616,
"WebHookId": 1234567890,
"siteLocation": "offshore",
"RefCountry": "DE",
"Country": "NL",
"WTG_SCID": "zyUL6lemDdM",
"WTG_ReferenceCountry": "DE",
"WTG_Material": "WTG_01",
"WTG_Description": "CS-042692A-#01",
"WTG_Quantity": 1,
"WTG_UnitOfMeasure": "EA",
"WTG_WTGType": "V136-4.0MW",
"WTG_RotorDiameter": "136 m",
"WTG_MarkRelease": "3E",
"WTG_NominalEffect": 4.2,
"WTG_HubHeight": 112,
"Cmpt_Material": "DRIVE_TRAIN_01",
"Cmpt_Quantity": 1,
"Cmpt_UnitOfMeasure": "EA",
"Cmpt_Description": "Drive Train MSI",
"Cmpt_MaterialGroup": 540,
"Cmpt_DerivedMaterial": 29219265,
"Cmpt_MaterialIndicator": "VAR"
}
]
You can concentrically use ReferenceCountry and WTGLevel arrays by taking one of them inside while qualifying by @2, in order to get cross join result ( 3 x 3 ) for returning the number of objects such as
[
{
"operation": "shift",
"spec": {
"ReferenceCountry": {
"*": {
"@2,WTGLevel": {
"*": {
"component": {
"*": { // partition the objects by indexes from two different levels
"@6,guid": "&4_&3_&1.guid",
"@6,WebHookId": "&4_&3_&1.WebHookId",
"@6,siteLocation": "&4_&3_&1.siteLocation",
"@4,RefCountry": "&4_&3_&1.RefCountry",
"@4,Country": "&4_&3_&1.Country",
"@2,WTG_SCID": "&4_&3_&1.WTG_SCID",
"@2,WTG_ReferenceCountry": "&4_&3_&1.WTG_ReferenceCountry",
"@2,WTG_Material": "&4_&3_&1.WTG_Material",
"@2,WTG_Description": "&4_&3_&1.WTG_Description",
"@2,WTG_Quantity": "&4_&3_&1.WTG_Quantity",
"@2,WTG_UnitOfMeasure": "&4_&3_&1.WTG_UnitOfMeasure",
"@2,WTG_WTGType": "&4_&3_&1.WTG_WTGType",
"@2,WTG_RotorDiameter": "&4_&3_&1.WTG_RotorDiameter",
"@2,WTG_MarkRelease": "&4_&3_&1.WTG_MarkRelease",
"@2,WTG_NominalEffect": "&4_&3_&1.WTG_NominalEffect",
"@2,WTG_HubHeight": "&4_&3_&1.WTG_HubHeight",
"Cmpt_Material|Cmpt_Quantity|Cmpt_UnitOfMeasure|Cmpt_Description": "&4_&3_&1.&",
"Cmpt_MaterialGroup|Cmpt_DerivedMaterial|Cmpt_MaterialIndicator|CapacityType": "&4_&3_&1.&"
}
}
}
}
}
}
}
},
{ // add an array wrapper at the topmost level while getting rid of newly formed object labels
"operation": "shift",
"spec": {
"*": "[]"
}
}
]